用于在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 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.