| Author |
 Topic Search  Topic Options
|
kbannon 
Groupie
Joined: 03 October 2002
Location: Ireland
Status: Offline
Points: 115
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: SQL Injection Filtering
Posted: 26 October 2005 at 6:12am |
|
What exactly is necessary to filter to prevent SQL Injection. I have
come across two different schools of thought and I want to nail it down
properly. I have always been led to believe that everything should be
screened and not to trust any user input but have recently been told
that its not necessary.
Is filtering (as with this forum) SQL keywords (SELECT, JOIN, etc.)
necessary or is escaping/filtering some of the main characters
sufficient?
|
|
I don't suffer from insanity, I enjoy every minute of it.
|
 |
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
Posted: 26 October 2005 at 7:26am |
|
The most effective/easiest way to stop sql injections IMHO is to use
stored procedures and pass all inputs through SQL Parameters. That way
the input is always treated as literal and will never be executed by the database engine.
You can do a similar thing in access by using parameters in your queries. I.e.
"SELECT * FROM tblUsers WHERE tblUsers.Username = @Username AND tblUsers.Password = @Password"
Then add the @Username and @Password parameters to your command object, not sure how you do that in classic asp
|
|
kbannon
Groupie
Joined: 03 October 2002
Location: Ireland
Status: Offline
Points: 115
|
Post Options
Thanks(0)
Quote Reply
Posted: 26 October 2005 at 8:09am |
I know that. What I want to know though is it necessary to filter SQL
keywords from ordinary statements that are not run by stored procs.
This forum's functions_filters.asp file does and converts any incidences of SELECT to select.
If its not necessary, why is it done?
Microsoft also recommend filtering anything including SQL keywords ( see here).
Edited by kbannon - 26 October 2005 at 8:10am
|
|
I don't suffer from insanity, I enjoy every minute of it.
|
|
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
Posted: 26 October 2005 at 10:47am |
|
Yes, you should filter keywords, even if you just replace select with select (just html encoding one of the e's).
But you don't even have to bother doing that if you're using paramatized queries.
|
|
kbannon
Groupie
Joined: 03 October 2002
Location: Ireland
Status: Offline
Points: 115
|
Post Options
Thanks(0)
Quote Reply
Posted: 26 October 2005 at 11:28am |
|
Right then - why should I as seemingly everyone else claims that as
long as single quotes, etc. are escaped, an SQL statement shouldnt
manage to run.
Most sites discussing SQL Injection claim that it is only necessary to
escape characters but make little or (generally) no reference to
removing SQL keywords. Are they mis-informing their readers but not
stating the obvious? Most sites say not to trust any input except
'known good input'. Presumably in the case of a forum or whatever, it
is all not 'known good input'.
I know this sounds strange but Im trying to find out what is the absolute best.
|
|
I don't suffer from insanity, I enjoy every minute of it.
|
|
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 26 October 2005 at 2:34pm |
|
Being as thorough as possible is always good, but I've found that if you treat all user input as literal, then you have no problem on the SQL.
I have a function that I run before passing any user supplied variable into the SQL server. You also check that your variables are the type that you expect. Numeric variables should be numeric and never pass table/ database names via variables.
I used to frequent a web site that got hit with SQL injection because he passed all queries via a variable. Very very very big no no.
|
|
|
|
theSCIENTIST
Senior Member
Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
|
Post Options
Thanks(0)
Quote Reply
Posted: 15 November 2005 at 7:05pm |
|
I also ALWAYS parse all input throught a function that converts all SQL reserved words just as (select) to (select), this way what the server gets will never be executed.
You mentioned escaping. Remember that some clever people can break out of it, and then they can send SQL to be executed.
Using stored procs or not I always parse all SQL words just to be on the safe side, and the penalties for this are negligible, minor increase in database stored data, since (select) uses less space than (select) and the web server gets also a minor hit on parsing all data, but as I said this is negligible.
|
|
|
|
padoxky
Groupie
Joined: 17 June 2004
Location: Nigeria
Status: Offline
Points: 78
|
Post Options
Thanks(0)
Quote Reply
Posted: 27 January 2006 at 11:36am |
Can you please write out the sample code as for me understand what you mean by that pharse out.
Thankz
|
|
NgWebDesigns
|
|
