[Techtalk] SQL learning pointers

[Carolyn Hicks]

On Mon, Dec 02, 2002 at 05:51:55AM -0800, Megan Golding wrote:
> 4. Any special tips on security from scripting abuses? My scripting
> is in PHP and a little Perl.

This'll probably come up in the documentation others have recommended, but
here's my #1 SQL security tip: *always* validate user input when
constructing SQL queries. This is to prevent an evil strategy known as
'SQL injection', where a user can pass SQL commands to your database
through the parameters your web form provides. 

For example, say you take the value a user types into a Surname field, and
construct an SQL string from it such as 

"SELECT * FROM contacts WHERE surname='$surname'"

A malicious user could type something like 

'; UPDATE contacts SET firstname='u R hAX0r3d'

into your Surname field. Without proper validation, that SQL could be
passed directly to your database and wreak all kinds of havoc. 

To avoid this, you just need to make sure all user input is escaped, with
any quotes and semicolons treated as literal characters. In perl, the
'quote' method of the DBI module takes care of this nicely, a la:

"SELECT * FROM contacts WHERE surname=".$dbh->quote($surname);

There's bound to be something similar in PHP.

-carolyn