用于在MySQL Query中转义不同变量类型的函数

I got sick of writing queries in my PHP as:

我厌倦了在PHP中编写查询:

"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.

escape()函数只调用mysql_real_escape_string() - 但它就在那里,所以我可以在以后扩展对其他数据库的支持。

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:

现在的好处是,我不需要担心我传递给查询的变量。然而,它提出了两个问题:

Am I handling each variable type properly?

我是否正确处理每种变量类型?

For what reason (if any) should I not do this?

出于什么原因(如果有的话)我不应该这样做?

4 个解决方案

#1

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

您正在寻找a)pepared语句和b)数据库抽象层(如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:

是毫无意义和危险的。 SQL代码和参数的明确分离要求您更加明确,同时让您在安全方面防止SQL注入:

"--- 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.

值得注意的是,根据您的需求和优先级,数据库领域中真正的供应商独立性介于艰难和不可能之间。因此,除非你愿意牺牲很多,否则尝试编写可移植的SQL可能会变得徒劳无功。对于MySQL,它甚至可以使用LIMIT子句启动,您将无法将其移植到SQL Server。

#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.

在PHP中,使用PDO来实现这一点,它的API比mysql_或mysqli_更加理智,而且,它可以在错误上抛出异常并做其他好事。

#3

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

你可能不应该这样做。原因如下:mysqli :: prepare或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.

至于你的函数本身,如果你有一些存储在字符串中的东西(比如“5”)你想要存储为int会发生什么?无论如何它仍会引用它。

#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?

减轻打字问题。虽然我的建议是,如果你接受来自表单的输入,为什么不设置Database :: prepare()来运行$ _REQUEST或$ _POST来清理它们,或者吐出一个副本?

This is how I do it:

我是这样做的:

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

#1