I see SQL code here that does not use Prepared Statements, and it concerns me. If you are querying data, there are very few times where it’s legitimate write straight SQL, so I wanted to list the arguments against PS’s here.
- I don’t know how. Using a Prepared Statement is actually simple and often easer to write. The point is to separate your data from the instructions to the SQL engine so it’s not possible to inadvertently do something bad. (This is known as “SQL injection”.) It’s worth the time to learn.
- I clean the data myself. You shouldn’t. The SQL engine already knows how to do this, provides a mechanism for it, and will always do it right, now and in the future.
- I control the inputs. This one is legitimate as long as we aren’t talking about text. If you are querying numbers, booleans, or something else that isn’t quoted and does not come from the user, go ahead and join the statement yourself. Otherwise, let the engine do it even when you’re providing the text.
Finally, consider this code:
dim sql as string = _
"SELECT * FROM my_table WHERE " + _
col1 = '" + fld1.Clean + "' OR " + _
(col2 LIKE '%" + fld2.Clean + "%' AND col3 = '" + fld3.Clean + "') OR " + _
"col4 = " + str( int1 )
If this were written as a Prepared Statement, it would be:
dim sql as string = _
"SELECT * FROM my_table WHERE " + _
"col1 = ? OR " + _
"(col2 LIKE ? AND col3 = ?) OR " + _
"col4 = ?"
If nothing else, isn’t that easier to read?
Use Prepared Statements.