PHOBOSLAB

Blog Home

Automatic Escaping of SQL Parameters

Escaping of SQL parameters in PHP is an needlessly tedious and error-prone process. Calling the right escaping function for every parameter that might be dangerous is a difficult task in a big project. This is just dangerous and calls for SQL-Injection-Attacks.

It’s obvious we need a uniform query function that automatically does the escaping for us. Perl-style parameter binding seems to be just the right choice. You can write your query, insert placeholders in the form of :n and pass the parameters to our function. The function automatically detects the datatype of the parameter for us and escapes it if needed.

query( 
    'SELECT posts 
        FROM blog 
        WHERE 
            status = :2
            AND created = :1',
    POST_STATUS_ACTIVE,
    '2007-08-02'
);

Ok, we need two things to get this working:

function query( $q, $params = array() ) {    
    $params = array_slice( func_get_args(), 1 );

    if( !empty( $params ) ) {
        $q = preg_replace('/:(\d+)/e', 'quote($params[$1 - 1])', $q );
    }

    return mysql_query( $q );
}

function quote( $s ) {
    if( $s === true ) {
        return 1;
    }
    else if( $s === false ) {
        return 0;
    }
    else if( is_int($s) || is_float($s) ) {
        return $s;
    }
    else {
        return "'".mysql_real_escape_string( $s )."'";
    }
}

The query function scans our SQL statement for placeholders, and calls the quote function in a regexp-callback for each one it finds. It is save to use placeholders in the parameters themself – they won’t be processed, as the regexp scans our string only once. So doing something like this is perfectly valid:

$searchString = "%ZOMFG :1 'asdf' :2%";
query( 'SELECT * FROM posts WHERE content LIKE :1', $searchString );

The final SQL-statement, that will be submitted to the database, looks like this:

SELECT * FROM posts WHERE content LIKE '%ZOMFG :1 ''asdf'' :2%'

Complete MySQL Class for PHP

If you’re interested, here’s the static MySQL class I use in my projects:
Static MySQL Class for PHP

This Class is lazy, which means it only connects to the database if it has to – e.g. the first time query() is called. It also returns results as associative arrays, rather than MySQL result resources.

Tuesday, August 21st 2007

4 Comments:

#1 – vvv – Saturday, March 8th 2008, 13:36

php.net/pdo for a nice abstraction layer to numerous db backends including prepared statements. needs php5.1 though.

#2 – craig – Wednesday, April 2nd 2008, 03:41

Confused as to what the $1 is doing in the

$q = preg_replace('/:(\d+)/e', 'quote($params[$1 - 1])', $q );

Also the subject parameter $q is unassigned?

#3Dominic – Thursday, April 3rd 2008, 20:38

$1 is the number matched with (\d+). $q is the function parameter for the sql statement.

#4MOin – Wednesday, September 24th 2008, 20:56

SQL db is the best one out there i wish someday i understand it all well but hey i am in learning process hoping for the best to understand php and sql all well :)

Post a Comment:

Comment: (Required)

(use <code> tags for preformatted text; URLs are recognized automatically)

Name: (Required)

URL:

Please type phoboslab into the following input field or enable Javascript. This is an anti-spam measure. Sorry for the inconvenience.