So I am retooling my stuff, and was wondering about my SQL injection protection methods.

the things I concern myself with are Single Quotes, which i escape out in MSsql with doubling which is how you're supposed to do it. so ' become ''
for numeric fields i test to make sure the input is numeric.
I also validate on secondary lookups, such as, pulling data from 1 recordset to lookup in another.

but im starting to wonder if there are any other things I need to block?
I am trying to think if there is a way to build a function that will do all my query cleaning for me, but i keep getting stuck on some things.
If i just test the variable input, and it is rejected, like in the case of a numeric for example: 0; DELETE FROM Orders;--
That would mean i need to do error handling on every single SQL query at the point of every query, which sucks if you ask me.

Anyone handle this a decent way for complex, multi-lookup systems? Without using some system specific garbage like Magic Quotes and such.

my thought is prevalidation, but then i still would need to specially handle the error gracefully by wrapping the lookup in an "If valid" test, and then display an explaination to the user. But when you have many lookups in a row, this becomes a bit confusing to deal with.

thoughts?

Of course the most immediate way is to NEVER allow a parameter to get to a SQL statement. The only real exception being in "Search This Site" kind of queries, but then you've limited it to one place you need to watch. A little set of functions around your DB can be helpful too ... if you're expecting and integer, then do something like $myInt = (int)$inputVal - or even better, $myInt = ($inputVall++)-- ...then you can easily trap for a zero when you wanted a non-zero and such...

I personally do a lot of "cases" where the passed parameter works its way through a switch statement and then I build the SQL not the parameter.

If you could put some examples of queries you might be expecting ... and are worried about ... that'd be helpful too

/p

(deleted the stupid graphic cause i was not thinking straight.)

The best example of what I need cleaned is data from rest queries.
But also from user input forms obviously.
But mostly its random input which both adds data to the database, or looks up. The biggest issue though are lookups that trigger further lookups. (secondary lookups)

The problem is not that I cant validate the data, obviously i can. I am trying to figure out a way to make the process easier and simpler to keep track of.

I guess i was just trying to figure out if there was a way to validate as im building the SQL string. I guess I really just am stuck with validation first, then building the sql string.  oh well.

i dont really know what I was thinking actually.

ok, to add to this...

the reason this is a pain in the ass for me is because of doing INSERT statements without knowing which columns the user submitted data for.

take this example.
fname
lname
age
city
state
zip
fav food

all but fname and last name are optional.
so the query generically looks like this:
insert into table (fname,lname,age,city,state,zip,favfood) values ('abe','bigpants',55,'tempe','az',12345,'ice cream')

the problem is that building this query is done in two parts, the column names, and the values.
if they are string, no problem, since the values would just end up being empty quotes.
but the numbers end up being empty, and as such the query would look like this:
insert into table (fname,lname,age,city,state,zip,favfood) values ('abe','bigpants',,'','',,'')

this obviously blows up because of the ,, for the numeric fields.

so either i wrap every single column in an IF statement, which frankly is rediculous on some of my inserts, which are huge.
or
I do a "select * from table where 1=0" which opens the table, returning no records, but does return the columns, and doesnt cost much. but the fact is i have never liked that, because I never understood what it really is doing.

im just trying to find a better way.

In

python

  you have sqlobject,
it maps sql statements to objects, also it abstracts the db
ROR has something similar

would you not better off to use something like that ?

I agree... is there no abstraction layer you can use to describe the dataset rather than talk directly to it? They exist in

PHP

  as well, but I wrote my own since I never knew where the source data was coming from... basically I throw and XML record into a function and it does the update for me. Metadata tells the function what the table is about, so that I can validate and refuse or supplement where necessary (a zero for a blank). As it happens, this metadata is also used to describe CRUD screens as well.

thats what ADO is, but i dont generally go that deep into using the built in functions. i just use the surface stuff like open and addnew, but keep most of the work in the query itself.

i guess its time for an ado refresher.