VB .net Run SQl query if

Question

Im working on my first useable practice app to help people I work with run simple queries without having to know SQL.

I have a simple form, with 4 textbox entries. columns, table, condition column, condition

what I am wanting is to run the query without the where statement if the condition column is blank, or run the conditional statement if there is something there.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles CMDQuery.Click

    If Columntxtbox.Text <> "" Then
        If SQL.HasConection = True Then
            If Conditioncolumntxtbox.Text = "" Then
                SQL.runquery("Select " & Columntxtbox.Text & " from " & tabletxtbox.Text)
            Else
                SQL.runquery("Select " & Columntxtbox.Text & " from " & tabletxtbox.Text & " Where " & Conditioncolumntxtbox.Text & " like'%" & conditiontxtbox.Text & "%'")

                If SQL.sqldataset.Tables.Count > 0 Then
                    DGVData.DataSource = SQL.sqldataset.Tables(0)
                End If
            End If
        End If
    End If

End Sub

what's happening is when I fill out all the fields, it works fine, when I try to enter just the column and table field, nothing happens. I'm thinking it has to do with the third If (if I change the = to <> then I get error with keyword like when column and city is filled and nothing when all are filled ) any help would be appreciated

thanks

You need a space before the WHERE clause (and probably after the LIKE operator). Said that. Start immediately a crash course on SQL Injection — Steve, Jun 24 '14 at 14:11
something must have happened when I copy and pasted, as I have the correct spaces there in my oringal code. Ill correct the post — bdrilling33, Jun 24 '14 at 14:16
@Steve good observation... this is prime SQL injection territory. You should use parameterized queries to be safer. — Dave C, Jun 24 '14 at 14:17

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

A part from the missing spaces, you don't show the results of the query when there is no condition

If Columntxtbox.Text <> "" Then
    If SQL.HasConection = True Then
        If Conditioncolumntxtbox.Text = "" Then
            SQL.runquery("Select " & Columntxtbox.Text & " from [" & tabletxtbox.Text & "]")
        Else 
            SQL.runquery("Select " & Columntxtbox.Text & " from [" & tabletxtbox.Text & _
                         "] Where [" & Conditioncolumntxtbox.Text & "] like '%" & _
                         conditiontxtbox.Text & "%'")
         End If
         If SQL.sqldataset.Tables.Count > 0 Then
            DGVData.DataSource = SQL.sqldataset.Tables(0)
         End If
    End If
End If

However keep in mind that it is a very bad practice to leave your user type directly the values, column names and other part of text that will be added to your sql text without checks. The user can type anything and you could find a malicious user that know about sql injection and ruin your life.

Given the context and the requirements, (a practice app) I think that you could ignore the problem for the moment. But again, in a real context scenario where data is the most important thing that your customers have, leaving holes of this magnitude is really unprofessional.

edited May 23 '17 at 12:05

Community

1
1

answered Jun 24 '14 at 14:19

Steve

213,761
22
232
286

Ill have to read up on SQL injection and how to prevent it. In the meantime, what do I need to do different to get the columns/tables query to work? – bdrilling33 Jun 24 '14 at 16:15
Do your column names contains spaces? Do the ColumnTextBox contains the names of more than one column? An example of you tablename and column name could help to spot errors. – Steve Jun 24 '14 at 17:57
in my text boxes i would enter the following: Column = * Table = Stores Condition column = City Condition = t – bdrilling33 Jun 24 '14 at 18:32
Look at the presence of spaces in your column name. If this is the case then you need to add square brackets around the table name and the column name used in the where condition. I have updated the answer for this scenario. – Steve Jun 24 '14 at 18:36
Jackpot. Thanks for your help. So when there are spaces in the column's name, it needs to have brackets around it as you did. Ill try to remember that lol. now to try to understand parameterized queries. – bdrilling33 Jun 24 '14 at 20:34
In your context it is not easy to use parameterized queries. That's because you cannot use a parameter to express the name of a column or the name of a table. Parameters works only for values in INSERT/UPDATE or for conditions values for WHERE clause. In your context the best approach is to read the SCHEMA of your database and present your users with comboboxes or lists with predefined values for tables and columns. Using the parameters only for the WHERE clause. Search how to read database schema. – Steve Jun 24 '14 at 20:37
And, if you can, avoid columns and tables with spaces. – Steve Jun 24 '14 at 20:40
dually noted. I don't know how that space got in there, ill have to go back and fix it, i mean its just one word, City ha. and I see what you mean about using a combobox, that makes more sense anyways, i will make the chance and again, thanks for your' help – bdrilling33 Jun 25 '14 at 14:15

VB .net Run SQl query if

1 Answers1