Database Discussion - SQL Injection Filtering

Print Page | Close Window

SQL Injection Filtering

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=17036
Printed Date: 29 March 2026 at 12:52pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL Injection Filtering

Posted By: kbannon
Subject: SQL Injection Filtering
Date 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.

Replies:

Posted By: Mart
Date 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

Posted By: kbannon
Date 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 ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag04/html/InjectionProtection.asp - see here ).

-------------
I don't suffer from insanity, I enjoy every minute of it.

Posted By: Mart
Date 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.

Posted By: kbannon
Date 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.

Posted By: ljamal
Date 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.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: theSCIENTIST
Date 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.

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Posted By: padoxky
Date 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