Function to escape different variable types in MySQL Query

Question

I got sick of writing queries in my PHP as:

"WHERE '" . Database::escape($var) . "'";

The escape() function just calls mysql_real_escape_string() - but it's there so I can extend support to other databases later.

Having to single quote strings in the query was making my code more cluttered. So my idea was to create an other function in my database class to 'prepare' a variable for a query:

static public function prepare($var)
{

    if (is_object($var) || is_array($var) ) {
        return " '" . Database::escape(serialize($var)) . "' ";

    } else if (is_bool($var)) {
        return ' ' . (int)$var . ' ';

    } else if (is_int($var)) {
        return ' ' . $var . ' ';

    } else if (is_string($var) || is_float($var)) {
        return " '" . Database::escape($var) . "' ";

    } else {
        throw new Exception('Unsupported variable type [' . gettype($var) . ']');
    }
}

Now the benefit here is that, I don't need to worry about which variables I pass to a query. However it raises two questions:

1. Am I handling each variable type properly?
2. For what reason (if any) should I not do this?

Answer 1

You probably shouldn't be doing this. Here's why: mysqli::prepare or PDO::prepare

As for your function itself, what happens if you have something stored in a string (say "5") that you want to store as an int? It'll still quote it anyway.

Answer 2

Yes, just use parameterised queries and it will Just Work. That is the right solution, and it's not terribly tricky.

In PHP, use PDO to do this, its API is much more sane than mysql_ or mysqli_ and moreover, it can throw exceptions on errors and do other nice things.

Answer 3

You are looking for a) pepared statements and b) a database abstraction layer (like PDO).

What you are trying to do on your own has been solved already, you should not roll your own implementation.

If you go down that road you'll notice that this:

"... WHERE '" . Database::escape($var) . "'"

is pointless and dangerous. A clear separation of SQL code and parameters requires you to be more explicit and gets you on the safe side against SQL injection the same time:

"--- WHERE SomeField = ?"  /* the parameter (?) will be filled elsewhere */

It's worth noting that true vendor-independence in the database field is somewhere between hard and impossible, depending on your needs and priorities. So trying to write portable SQL could turn out as an exercise in futility unless you are willing to sacrifice a lot. For MySQL it starts even with the LIMIT clause, which you will find impossible to port to, say, SQL Server.

Answer 4

You can try

$sql = "SELECT * FROM SomeTable WHERE userid = '{Database::prepare($userid}'";

to alleviate the typing issues. Though my suggestion is that if you are accepting inputs from a form, why not setup Database::prepare() to run through the $_REQUEST or $_POST to clean them up, or spit out a copy?

This is how I do it:

$safe_post = InputCleaner::clean($_POST);
$sql = "SELECT * FROM table WHERE userid = {$safe_post['userid']}";