Re: TWIG SQL query bugs

[Ben Laurie <ben () algroup co uk>]

Ryan Fox wrote:
> One more point here.
>
> > Simply adding a quote is not the proper way to handle this in PHP.
>
> Not really.  There are other malicious characters that can be used in sql
> statements.  The one in the front of my mind is ";", used to terminate a
> query and begin a new one.  Think of $IDNumber=";drop database foo".  (This
> can be helped by using a database with proper access controls set up, so the
> user the web process normally connects as does not have permission to drop a
> database.  I don't know if this product sets itself up like that by
> default).
>
> Good programming practice is to code a function specifically to strip any
> possible malicious characters out of strings, and wrap it around every
> variable put into a query, whether it should be user-supplied or not.
> Addslashes is a good function to call from your stripping function, but it
> should not be your only line of defense.

Most DB interfaces provide a quoting function for precisely this purpose
(in Perl DBD/DBI, you use $dbh->quote($thing), for example). I don't
know PHP, so I can't say whether it does, but it surely should.

Alternatively, the really simple safe way to handle it is to use SQL
placeholders, so the dangerous value is not included in the statement at
all. That is, your SQL looks like this:

SELECT foo, bar FROM wotsit WHERE thingy=?;

and the ? is supplied as a separate value. These are normally handled
right at the back end long after any parsing has been done, so no amount
of weird quoting can bit you.

> From memory, you do this like so in Perl:

my $sth=$dbh->prepare('SELECT foo, bar FROM wotsit WHERE thingy=?');
$sth->execute($thingy);

Cheers,

Ben.

--
http://www.apache-ssl.org/ben.html

"There is no limit to what a man can do or how far he can go if he
doesn't mind who gets the credit." - Robert Woodruff