WebApp Sec mailing list archives
Re: SQL Injection Basics
From: Mark Curphey <mark () curphey com>
Date: 11 Feb 2003 20:29:42 -0800
Jim, The OWASP web site is in a bad way; mainly because we (well David Raphael) have been building out the long term site, a Java based portal which will dynamically build the site with the latest code and documentation (all driven through DocBook). Its just not worth maintaining the html version of the site as this will come on-line very shortly. Our apologies for that but this will be better all around in the long term. You can look at the CVS code for Filters via Web CVS at http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/owasp/filters/ The next version of the OWASP Guide is being baked and there are code examples in the brew. This is draft and DocBook but Appb.xml and Appc.xml maybe of interest. http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/owasp/guide/ The DocBook for Alex's presentation about Filters is also in the Filters mailing list archives at http://sourceforge.net/mailarchive/forum.php?thread_id=1565817&forum_id=12809 Cheers Mark On Tue, 2003-02-11 at 19:10, Jim McGarvey wrote:
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 ----- 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 BasicsMy personal favourite method when working with IIS/ASP is to handle alldata.retrieval. using only the primary key, which is always an integer. Fortunately, digits do not pose a security threat in any way shape orform.in VBScript/ASP, sql = "SELECT * FROM myTable WHERE id=" & CInt(Request.QueryString("id"))From my experience, this is unbeatable. However there is one obvious,fairly big drawback - it only works on integers - hence why it is onlyusedfor retrieval. Mark. -----Original Message----- From: Ken Anderson [mailto:ka () pacific net] Sent: Wednesday, February 12, 2003 4:21 AM Cc: webappsec () securityfocus com Subject: Re: SQL Injection Basics With mysql, you can use double quotes instead of single quotes around strings. http://www.mysql.com/doc/en/Extensions_to_ANSI.html You can then allow single quotes in user input for things like last name of "O'Malley" in java: foo = req.getParameter("foo"); sanitize(foo); // disallow other bad stuff [%()"\...] sql = "select * from users where username = "\"+foo+"\""; If the value of foo is "'foo", it's not a problem. mysql> select lastname from users where lastname = "O'Brien"; +--------------+ | lastname | +--------------+ | O'Brien | | O'Brien | +--------------+ Ken A. dreamwvr () dreamwvr com wrote:On Tue, Feb 11, 2003 at 04:35:50PM +0100, Sverre H. Huseby wrote:Consider a system talking to PostgreSQL (or MySQL or any other database that accepts C-style backslash escapes in string constants). If the developer just thinks about the ticks, he may (givenWell IMO why not simply filter out all non alpha and integers. logging everything else and not allowing to form a SQL statement at all? Then allow very select special characters like ; only in a specific position. Just my 2 cents. Best Regards, dreamwvr () dreamwvr com******************************* 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. *************************************************************************
Current thread:
- Re: SQL Injection Basics, (continued)
- Re: SQL Injection Basics NetNinja (Feb 09)
- Re: SQL Injection Basics Dirk Gomez (Feb 10)
- RE: SQL Injection Basics Logan F.D. Greenlee (Feb 10)
- RE: SQL Injection Basics Brass, Phil (ISS Atlanta) (Feb 11)
- RE: SQL Injection Basics Eric Appelboom (Feb 11)
- Re: SQL Injection Basics Kevin Spett (Feb 11)
- RE: SQL Injection Basics Patrick Debois (Feb 11)
- RE: SQL Injection Basics Logan F.D. Greenlee (Feb 11)
- RE: SQL Injection Basics Mark Mcdonald (Feb 11)
- Re: SQL Injection Basics Jim McGarvey (Feb 11)
- Re: SQL Injection Basics Mark Curphey (Feb 11)
- Re: SQL Injection Basics Jim McGarvey (Feb 12)
- Re: SQL Injection Basics dreamwvr () dreamwvr com (Feb 12)
- Re: SQL Injection Basics Jim McGarvey (Feb 11)
- RE: SQL Injection Basics David Cameron (Feb 11)
- RE: SQL Injection Basics Mark Mcdonald (Feb 11)
- RE: SQL Injection Basics Jason Benson (Feb 12)
- RE: SQL Injection Basics David Cameron (Feb 12)
- Re: SQL Injection Basics Alex Russell (Feb 12)
- RE: SQL Injection Basics David Cameron (Feb 12)
- RE: SQL Injection Basics Brass, Phil (ISS Atlanta) (Feb 13)
- Re: SQL Injection Basics Bart McKinnley (Feb 14)