Preventing SQL injection attacks

[Martin Lucas-Smith]

Can anyone provide any suggestions/URLs for best-practice approaches to
preventing SQL injection? There seems to be little on the web that I can
find on this.


Martin Lucas-Smith www.geog.cam.ac.uk/~mvl22

https://www.lucas-smith.co.uk

Senior Computing Technician (Web Technician)
Department of Geography, University of Cambridge (01223 3)33390

& Webmaster, SPRI
Scott Polar Research Institute, University of Cambridge

[Justin Koivisto]

Re: Preventing SQL injection attacks

Martin Lucas-Smith wrote:[color=blue]
>
> Can anyone provide any suggestions/URLs for best-practice approaches to
> preventing SQL injection? There seems to be little on the web that I can
> find on this.[/color]

The php manual actually has a section that talks about this a bit:

http://www.php.net/manual/en/security.database.php

--
Justin Koivisto - spam@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.

[Andy Hassall]

Re: Preventing SQL injection attacks

On Mon, 18 Aug 2003 13:33:10 +0100, Martin Lucas-Smith <mvl22@cam.ac.u k> wrote:
[color=blue]
>Can anyone provide any suggestions/URLs for best-practice approaches to
>preventing SQL injection? There seems to be little on the web that I can
>find on this.[/color]

Using what database?

Any database with a moderately well designed interface supports placeholders
(a.k.a. bind variables). These separate the text of the SQL from the values you
wish to use within the statement. Using placeholders eliminates the possibility
of SQL injection attacks, as your values are never interpreted as SQL.

e.g.

INSERT INTO wherever (col1, col2) values (?, ?)

You'd 'prepare' this statement - the database parses the SQL, and discovers
two placeholders where values need to go.

You then 'bind' two values to it - the values you want are associated with the
placeholders. No need to think about quoting, or 'special' characters, it's
just data, not SQL.

Then 'execute' it - the statement runs, it has the necessary data from the
bind step, and it's done.

Constants are OK in SQL, but variable data shouldn't be present in an SQL
statement; anything that's variable should be a placeholder.


Unfortunately MySQL, commonly used with PHP, forces you to intermingle SQL
with data.

e.g.

INSERT INTO wherever (col1, col2) values ('$x1', '$x2')

So, you then need to start worrying about whether $x1 and $x2 contain single
quotes or null characters.

If $x1='a', and $x2 contained:

'); DELETE from wherever;

Then because of the further design flaw in the PHP/MySQL extension which lets
it exeute multiple statements with a single call to mysql_query(), it actually
ends up executing:

INSERT INTO wherever (col1, col2) values ('a', ''); DELETE FROM wherever; )

Which runs the insert, runs the delete, then ends in a syntax error, but the
prior statements have already run. Your data is now gone.

You need to escape single quotes and null values in data if you're passing it
to MySQL. Use addslashes() or mysql_escape_st ring(), and use it exactly once,
else you end up with escaped slashes in your database. PHP has the
'magic_quotes' options that escape values coming from forms, which can often be
the cause of double-escaping.

After escaping, you'd get:

INSERT INTO wherever (col1, col2) values ('a', '\'); DELETE FROM wherever; )')

Which is valid single insert.

For numbers, you don't want to quote them, so ensure they are actually numeric
before passing them into the SQL, with is_numeric().

Or use a database abstraction layer that emulates placeholders for you, if
your database doesn't support them natively, such as Pear DB. Or roll your own;
you can knock something up with sprintf, and wrap it around your database
calls.

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)