Re: SQL Injection Basics

Mark:

I think you meant to first sanitize CInt(Request.QueryString("id")) to make sure it's an integer. If I'm not mistaken, you've given a perfect example of code that is susceptible to SQL Injection mischief:

http://www.yourserver.com/yourscript.asp?id=1+OR+1=1 makes your query:
SELECT * FROM myTable WHERE id=1 OR 1=1

... which would return all the records from the table, instead of the one intended, and that's just the beginning of what you could do.

You need to be sure to verify that the string you're appending to your sql query is indeed just a number, and it's probably a good practice to do this right before you execute the query. Just because you hardcoded a numeric id into a query string on your page (or validated a form with javascript) doesn't mean someone won't pass something else to the server.

I don't recall much VBScript, but maybe this would work: sql = "SELECT * FROM myTable WHERE id=" & CInt(Request.QueryString("id")).toNumber().toString()

...or to be on the safe side, make it a function: clean_id_str = sanitizeID(CInt(Request.QueryString("id"))) sql = "SELECT * FROM myTable WHERE id=" & clean_id_str

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related securityfocus.com Links bugtraq certification focus-bsd focus-ids focus-ih focus-linux focus-ms focus-sun focus-unix-other focus-virus forensics incidents libnet linux-secnews ms-secnews pen-test secpapers secprog sectools secureshell security-basics securityjobs sf-news vuln-dev webappsec Request more information about Pantek

But you're correct that for data retrieval, as long as you make sure the id is an integer, your method will always work and can be secure for queries where you are selecting on an integer column, as long as you make sure what you put in the sql statement is just numeric.

Generally speaking though, I personally prefer having one consistent, standard solution for database interaction within a specific application, like those mentioned by others on the list, that work for both data retrieval and data manipulation. Robert Nilsen mentioned "prepared statements, a.k.a. bind variables/parameters," which if you've ever used perl/DBI/DBD to access a database like mysql, works great, and you don't even have to think about security, just remember to pass any variables in as bind variables. Here's an example:

        use DBI;
        $dbh = DBI->connect("DBI:$dbsystem:$dbname", $dbuser, $dbpass);
        $sth = $dbh->prepare("INSERT INTO contacts (name,email) VALUES
(?,?)");
        $sth->execute($name,$email);

Some people mentioned that if you use prepared statements "you lose compatibility with different database systems." If I understand DBI correctly, it was designed so the code written above will work with any database with only one modification: updating the global variable $dbsystem from "mysql" to "oracle" for example. Of course if you the database you're switching to has different "features" than the one you're switching from, there's nothing DBI can do to help you with that.

So I would go so far as to say using DBI's prepared statements for database interaction is considered a "best practice" for securely accessing a database in perl. I'm curious if there's a central repository of other such "best practices" for other languages (ASP, java, PHP, etc.). I went to http://www.owasp.org/filters/ as suggested, hoping it would have that information, but all I found was a brief description of the project and a note that indicates the alpha release of the draft plan for the project was expected in late 2002. Is it worth installing cvs to see what's in there?

Thanks,
-Jim

• Original Message ----- From: "Mark Mcdonald" <m.mcdonald@cgl.com.au> To: "'Ken Anderson'" <ka@pacific.net> Cc: <webappsec@securityfocus.com> Sent: Tuesday, February 11, 2003 4:44 PM Subject: RE: SQL Injection Basics

> My personal favourite method when working with IIS/ASP is to handle all
data
> .retrieval. using only the primary key, which is always an integer.
form.

>

>

>

>

>

>
>
>

>

>

>

>

>

>
>

>
>

> ******************************* DISCLAIMER ******************************

>