Re: Scripting Languages and Secure Coding + code

["David M. Wilson" <dw-securecoding.org () botanicus net>]

On Fri, Dec 05, 2003 at 01:37:36PM +1100, Louis Solomon [SteelBytes] wrote:

> I use the php 4.3.x func mysql_real_escape_string()

> eg, $query = 'select * from users where
> username="'.mysql_real_escape_string($_REQUEST['username']).'" and password=
> ....

> this way it doesn't matter what the user enters.


Take a look at <http://botanicus.net/dw/tmp/sql.inc.php.txt>.

Basically, it is a wrapper around sprintf() that gives you an 'S'
conversion, which will mysql_escape_string() a passed string. It is more
a utility of convenience and clarity than anything else:

   $query = ssprintf(
      'SELECT '.
         'tbl1.a * (100 * tbl2.c) AS commission, '.
         'blah, foo, bar '.

      'FROM tbl1, tbl2 '.
      'WHERE '.
         'tbl1.thing = tbl2.flarb '.
         'AND tbl2.blurp LIKE "%%%S%%" '.

      'LIMIT 50'
   );


   Or how about:

   $is_authorized = (bool) sql_column(
      'SELECT COUNT(*) FROM members WHERE username = "%S" AND password = "%S"',

      $username,
      $password
   );



Both are readable, easily repeatable, and relatively robust. The problem
with APIs where you do your own escaping is just that. Lots of people
don't do it, or don't remember to do it.

By wrapping the escaping into a utility library that requires you to use
it in order to get access to the easy-to-use functions, some kind of
enforcement occurs, and the programmer will naturally write code that
doesn't have SQL escaping problems.


A better solution is to use a native database API that properly
abstracts the query from the data. Python's DB-API can do this:

   cursor.execute(
      """
      INSERT INTO foo(bar, baz, flarble, intcol)
      VALUES (%s, FUNC(%s), %s, %s)
      """,

      ( userdata.bar,
        userdata.baz,
        userdata.flarble, 
        userdata.intcol )
   )


This is more or less what I was trying to rip off with sql.inc.php. It
means you must try really hard if you want to directly pass unescaped
user data to your SQL server (in the MySQL case, you must play with the
DBAPI object internals or import a lower-level module directly).


> of course with different DBs, the escaping of chars maybe different.

AFAIK ADODB handles this uniformly in PHP, and most other database
middleware layers will do it for you too.


> > I am not sure why you "stripslashes" for the user name
> > and password.

This is a horrific way of damaging your input data. Your $username no
longer represents what the user entered.


> > If the slashes are there (due to
> > magic_quotes being enabled),

Another feature which should never, ever have been. "Let's just escape
all input data in case the programmer doesn't know what he is doing and
passes it to something dangerous."


David.