Best Practice for SQL Statements

[dinosaur_uk]

HI folks,

I am in need of some advice, I need to know what is the best way of forming SQL Statements.

Currently I am simply doing:

 Dim i As Integer
            strSQL = "SELECT "
            For i = 0 To Me.lstFields.SelectedIndices.Count - 1
                strSQL &= "[" & Me.lstFields.Items(Me.lstFields.SelectedIndices(i)) & "],"
            Next
            strSQL = strSQL.TrimEnd(",")
            strSQL += " FROM " & Me.lstTables.SelectedItem.ToString

[jmcilhinney]

You should always avoid using string concatenation if possible, but if you are allowing the user to select the columns then that's not possible because you cannot use parameters for identifiers, only values. You just need to make absolutely sure that the identifier you're inserting is valid to avoid SQL injection attacks. If you have added the values yourself then that's a fairly safe bet, but if the user is entering the name freehand then you really need to validate it first.

On a different note, wouldn't this:

Me.lstFields.Items(Me.lstFields.SelectedIndices(i))

be better written like this:

Me.lstFields.SelectedItems(i)

?

[dinosaur_uk]

I've seen one of your post before which had an example of a good practive SQL statement builder?

[jmcilhinney]

If your table and column names are constant and you're just inserting column values then you should always use parameters and only parameters. If you're inserting table and column names then you have no choice but to use string concatenation or the like. As long as you ensure that each table and column name is valid then you have nothing to worry about. If you have hard-coded those names or, better yet, retrieved them from the database yourself then you can be sure that they're not going to inject malicious SQL code into your statement. Otherwise you would need to validate them first.

If you need to let the user specify column names AND values then you will have to use string concatenation for the names but you should still use parameters for the values.

I have a dim recollection of that thread you're talking about but it would have been possibly two years ago and I wouldn't like to try to track it down.