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?
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
[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
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
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
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.