RE: SQL Injection Basics

[Mark Mcdonald <m.mcdonald () cgl com au>]

Jim,

You're right, if the user doesn't enter an integer CInt will just bail
throwing a "Type Mismatch".

VBScript isn't as OO as what you've said below, and you would also want the
CInt() calls .last. if you were santizing, as otherwise you'll get the same
problem as above, non-integer values will fail before the sanitizing
function was even called.

Another VBScript function relevant is isNumeric for numeric values.  Still,
I prefer PHP dev. with the better inbuilt functions there :)

You actually answered an un-asked question in head below too, on using bind
variables in MySQL, since it's not possible natively, has anyone done their
own implementation?  IIRC, MySQL4+ actually has bind variable support, but
I'm not a big fan of using development servers in production.

        $sth = $dbh->prepare("INSERT INTO contacts (name,email) VALUES
(?,?)");
        $sth->execute($name,$email);

Might be time to install mod_perl :)

Has anyone made a sanitising function for VBScript they would like to share?

Thanks,
Mark.

-----Original Message-----
From: Jim McGarvey [mailto:mcga0031 () umn edu]
Sent: Wednesday, February 12, 2003 11:11 AM
To: webappsec () securityfocus com
Subject: 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

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


******************************* DISCLAIMER ****************************** 
This e-mail and any attachments to it are confidential. 
If you receive them in error, please tell us immediately and delete them. 
You must not retain, distribute, disclose or otherwise use any information
contained in them. 

Before opening or using any attachments with this e-mail you should check
them for viruses and other defects. The sender does not warrant that they 
will be free from computer viruses or other defects.
*************************************************************************