PreparedSQLStatement and SQL Injection attacks

Using prepared is a good way to prevent SQL injection attacks but do they still help if you don’t use the Bind/BindType methods?

Example:
dim sql as string = “select * from SomeTable WHERE widget1 > wdiget2 or green < red;” //No ? for Binding.
dim stmt as PreparedSQLStatement
stmt = db.Prepare(sql)
dim rs as recordset = stmt.SQLSelect

Even though I am not using Bind/BindType will this prevent a SQL Injection attack?

Since there nothing being injected from user entered values it wont matter

Sorry I wasn’t more clear and included a poor example.
The query will be dynamic and will user input.

The methods we use now have the ‘Where’ part passed in as an argument and often times is build with user entered data.
A better example would be:

dim ars() as string
ars.append "select * From T_Project "
if sCriteria.Trim <> “” then
ars.append "WHERE " + sCriteria //criteria is a passed in argument
end if

dim s as string = ars.JoinSQL
dim stmt as PreparedSQLStatement
stmt = db.Prepare(sql)
dim rs as recordset = stmt.SQLSelect

//do stuff

prepared statement only helps if you bind values from user.
Not if you add them to where clause.

[quote=243814:@Robert Smith]Sorry I wasn’t more clear and included a poor example.
The query will be dynamic and will user input.

The methods we use now have the ‘Where’ part passed in as an argument and often times is build with user entered data.
A better example would be:

dim ars() as string
ars.append "select * From T_Project "
if sCriteria.Trim <> “” then
ars.append "WHERE " + sCriteria //criteria is a passed in argument
end if

dim s as string = ars.JoinSQL
dim stmt as PreparedSQLStatement
stmt = db.Prepare(sql)
dim rs as recordset = stmt.SQLSelect

//do stuff[/quote]

AH
You cant do the “where part” as a single prepared statement like you showed
That just builds up a sql statement and using prepared is pointless in that case

Doing what you posted does mean someone could write, for their where clause, “1” and they would get EVERY column from T_Project :stuck_out_tongue:
And they could also possibly write “1; delete from T_Project”
And now you have nicely facilitated exactly the kind of injection that a prepared statement was designed to prevent

Depending on how people are getting access there are a handful of things you might do

  1. not allow free text queries and provide some kind of “query builder” that builds up a set of data that you can then use to build a proper prepared statement with bind values

  2. allow free text but make sure that anyone logged in and using that cannot insert , delete, update, create tables or drop tables. Make it so they ONLY have SELECT privileges. There are still risks doing this but at least you can do “free text queries”

Something else you could try is sending the text through a method to see if certain word combinations would constitute a possible SQL Injection Risk. I also include my table names as part of this check to ensure that the only SQL statements that touch the database are the statements in the code.