问题:如何防止PHP中进行SQL注入?

如果将用户输入未经修改地插入到SQL查询中,则该应用程序容易受到 SQL注入的攻击,例如以下示例:

$unsafe_variable = $_POST['user_input']; 

mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

那是因为用户可以输入类似value'的内容;DROP TABLE表;-,查询变为:

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

如何防止这种情况发生?

标签:php,mysql,sql,security,sql-injection

Q: How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:

$unsafe_variable = $_POST['user_input']; 

mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

What can be done to prevent this from happening?

回答1:

使用准备好的语句和参数化查询。这些SQL语句与任何参数分开发送到数据库服务器并由其解析。这样,攻击者就不可能注入恶意SQL。

您基本上有两种选择可以实现这一目标:

  1. 使用 PDO (对于任何受支持的数据库驱动程序):

    $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
    
    $stmt->execute([ 'name' => $name ]);
    
    foreach ($stmt as $row) {
        // Do something with $row
    }
    
  2. 使用 MySQLi (对于MySQL):

    $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
    $stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
    
    $stmt->execute();
    
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        // Do something with $row
    }
    

如果要连接到除MySQL以外的数据库,则可以引用特定于驱动程序的第二个选项(例如,pg_prepare()pg_execute() (对于PostgreSQL)。 PDO是通用选项。

正确设置连接

请注意,使用PDO访问MySQL数据库 real 时,默认情况下不使用准备好的语句。要解决此问题,您必须禁用对准备好的语句的仿真。使用PDO创建连接的示例是:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'password');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

在上面的示例中,错误模式不是严格必需的,但建议添加它。这样,当出现问题时,脚本不会因FatalError(致命错误)而停止。而且,它使开发人员有机会捕获任何错误,而这些错误PDOException s会被抛出 n。

什么是强制,但是第一行是setAttribute()行,它告诉PDO禁用模拟的准备好的语句并使用 real 陈述。这样可以确保在将语句和值发送到MySQL服务器之前,不会对PHP进行解析(这样可能会使攻击者没有机会注入恶意SQL)。

尽管您可以在构造函数的选项中设置charset,但请务必注意,PHP的"较旧"版本(在5.3.6之前)静默忽略了DSN中的字符集参数

说明

您传递给prepare的SQL语句由数据库服务器解析和编译。通过指定参数(在上面的示例中为?或诸如:name之类的命名参数),您可以告诉数据库引擎要在何处进行过滤。然后,当您调用execute时,准备好的语句将与您指定的参数值组合在一起。

这里重要的是参数值与已编译的语句(而不是SQL字符串)组合在一起。 SQL注入通过在创建要发送到数据库的SQL时欺骗脚本使其包含恶意字符串来起作用。因此,通过将实际的SQL与参数分开发送,可以减少因意外获得最终结果的风险。

使用准备好的语句发送的任何参数都将被视为字符串(尽管数据库引擎可能会进行一些优化,因此参数最终也可能以数字结尾)。在上面的示例中,如果$name变量包含'Sarah';从员工中删除结果仅是搜索字符串"'Sarah';从员工中删除",您将不会得到空表

使用准备好的语句的另一个好处是,如果您在同一会话中多次执行同一条语句,则该语句将仅被解析和编译一次,从而使您获得一些速度提升。

哦,既然您询问如何插入,这是一个示例(使用PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute([ 'column' => $unsafeValue ]);

准备好的语句可以用于动态查询吗?

虽然您仍可以使用准备好的语句作为查询参数,但不能对动态查询本身的结构进行参数化,也不能对某些查询功能进行参数化。

对于这些特定情况,最好的办法是使用白名单过滤器来限制可能的值。

// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
   $dir = 'ASC';
}

A1:

Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

  1. Using PDO (for any supported database driver):

    $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
    
    $stmt->execute([ 'name' => $name ]);
    
    foreach ($stmt as $row) {
        // Do something with $row
    }
    
  2. Using MySQLi (for MySQL):

    $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
    $stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
    
    $stmt->execute();
    
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        // Do something with $row
    }
    

If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (for example, pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

Correctly setting up the connection

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'password');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error when something goes wrong. And it gives the developer the chance to catch any error(s) which are thrown as PDOExceptions.

What is mandatory, however, is the first setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).

Although you can set the charset in the options of the constructor, it's important to note that 'older' versions of PHP (before 5.3.6) silently ignored the charset parameter in the DSN.

Explanation

The SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) you tell the database engine where you want to filter on. Then when you call execute, the prepared statement is combined with the parameter values you specify.

The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn't intend.

Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name variable contains 'Sarah'; DELETE FROM employees the result would simply be a search for the string "'Sarah'; DELETE FROM employees", and you will not end up with an empty table.

Another benefit of using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.

Oh, and since you asked about how to do it for an insert, here's an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute([ 'column' => $unsafeValue ]);

Can prepared statements be used for dynamic queries?

While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.

For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.

// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
   $dir = 'ASC';
}

回答2:

不建议使用的警告::该答案的示例代码(如问题的示例代码)使用PHP的MySQL扩展名,该扩展名在PHP 5.5.0中已弃用,并在PHP 7.0中被完全删除.0。

安全警告:此答案与安全最佳做法不符。 转义不足以防止SQL注入,请使用准备好的语句。使用以下概述的策略需要您自担风险。 (此外,在PHP 7中删除了mysql_real_escape_string()。)

如果您使用的是最新版本的PHP,下面概述的mysql_real_escape_string选项将不再可用(尽管mysqli::escape_string是现代的等效项)。如今,mysql_real_escape_string选项仅适用于旧版本PHP上的遗留代码。


您有两个选择-在unsafe_variable中转义特殊字符,或使用参数化查询。两者都可以保护您免受SQL注入的侵害。参数化查询被认为是更好的做法,但是在使用它之前,需要先在PHP中更改为更新的MySQL扩展。

我们将介绍先转义的较低冲击线。

//Connect

$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);

mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

//Disconnect

另请参见 mysql_real_escape_string 函数的详细信息。

要使用参数化查询,您需要使用 MySQLi 而不是 MySQL 函数。要重写您的示例,我们将需要以下内容。

<?php
    $mysqli = new mysqli("server", "username", "password", "database_name");

    // TODO - Check that connection was successful.

    $unsafe_variable = $_POST["user-input"];

    $stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");

    // TODO check that $stmt creation succeeded

    // "s" means the database expects a string
    $stmt->bind_param("s", $unsafe_variable);

    $stmt->execute();

    $stmt->close();

    $mysqli->close();
?>

您将要阅读的关键功能将是 mysqli::prepare

而且,正如其他人所建议的那样,您可能会发现有用/轻松地使用 PDO < / a>。

请注意,您所询问的案例是一个相当简单的案例,而更复杂的案例可能需要更复杂的方法。特别是:

A2:

Deprecated Warning: This answer's sample code (like the question's sample code) uses PHP's MySQL extension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.

Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also, mysql_real_escape_string() was removed in PHP 7.)

If you're using a recent version of PHP, the mysql_real_escape_string option outlined below will no longer be available (though mysqli::escape_string is a modern equivalent). These days the mysql_real_escape_string option would only make sense for legacy code on an old version of PHP.


You've got two options - escaping the special characters in your unsafe_variable, or using a parameterized query. Both would protect you from SQL injection. The parameterized query is considered the better practice but will require changing to a newer MySQL extension in PHP before you can use it.

We'll cover the lower impact string escaping one first.

//Connect

$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);

mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

//Disconnect

See also, the details of the mysql_real_escape_string function.

To use the parameterized query, you need to use MySQLi rather than the MySQL functions. To rewrite your example, we would need something like the following.

<?php
    $mysqli = new mysqli("server", "username", "password", "database_name");

    // TODO - Check that connection was successful.

    $unsafe_variable = $_POST["user-input"];

    $stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");

    // TODO check that $stmt creation succeeded

    // "s" means the database expects a string
    $stmt->bind_param("s", $unsafe_variable);

    $stmt->execute();

    $stmt->close();

    $mysqli->close();
?>

The key function you'll want to read up on there would be mysqli::prepare.

Also, as others have suggested, you may find it useful/easier to step up a layer of abstraction with something like PDO.

Please note that the case you asked about is a fairly simple one and that more complex cases may require more complex approaches. In particular:

  • If you want to alter the structure of the SQL based on user input, parameterized queries are not going to help, and the escaping required is not covered by mysql_real_escape_string. In this kind of case, you would be better off passing the user's input through a whitelist to ensure only 'safe' values are allowed through.
  • If you use integers from user input in a condition and take the mysql_real_escape_string approach, you will suffer from the problem described by Polynomial in the comments below. This case is trickier because integers would not be surrounded by quotes, so you could deal with by validating that the user input contains only digits.
  • There are likely other cases I'm not aware of. You might find this is a useful resource on some of the more subtle problems you can encounter.

回答3:

这里的每个答案仅涵盖部分问题。实际上,可以动态添加到SQL中的四个不同查询部分:-

  • 一个字符串
  • 一个数字
  • 标识符
  • 语法关键字

准备好的语句仅覆盖其中两个。

但是有时我们必须使查询更加动态,同时还要添加运算符或标识符。因此,我们将需要不同的保护技术。

通常,这种保护方法基于白名单

在这种情况下,每个动态参数都应在脚本中进行硬编码,然后从该集合中进行选择。例如,要进行动态排序:

$orders  = array("name", "price", "qty"); // Field names
$key = array_search($_GET['sort'], $orders)); // if we have such a name
$orderby = $orders[$key]; // If not, first one will be set automatically. 
$query = "SELECT * FROM `table` ORDER BY $orderby"; // Value is safe

为简化此过程,我编写了白名单帮助程序功能,它可以在一行中完成所有工作:< / p>

$orderby = white_list($_GET['orderby'], "name", ["name","price","qty"], "Invalid field name");
$query  = "SELECT * FROM `table` ORDER BY `$orderby`"; // sound and safe

还有另一种保护标识符的方法-转义,但我宁愿坚持将白名单作为一种更可靠和明确的方法。但是,只要您带引号的标识符,就可以转义引号字符以使其安全。例如,默认情况下,对于mysql,您必须将引号字符加倍以对其进行转义。对于其他DBMS,转义规则将有所不同。

不过,SQL语法关键字(例如ANDDESC等)仍然存在问题,但是在这种情况下,白名单似乎是唯一的方法。 / p>

因此,一般性建议可以写成

  • 任何表示SQL数据文字的变量(或简单地说-SQL字符串或数字)必须通过准备好的语句添加。没有例外。
  • 任何其他查询部分,例如SQL关键字,表或字段名或运算符-必须通过白名单进行过滤。

更新

尽管就SQL注入保护的最佳做法达成了普遍共识,但还有许多不良做法。。其中有些根深蒂固地植根于PHP用户的心中。例如,在此页面上,(尽管对大多数访问者不可见)超过80个已删除答案-由于质量低劣或推广不良和过时的做法而被社区删除。更糟糕的是,一些错误的答案并未被删除,反而会蒸蒸日上。

例如,有(1) are(2) still(3) 许多(4) 答案(5),包括第二高的答案建议您手动转义字符串-一种已被证明不安全的过时方法。

或者有一个更好的答案,它暗示了另一种字符串格式化方法,甚至称赞它是最终的灵丹妙药。当然不是。这种方法并不比常规的字符串格式更好,但是它保留了所有缺点:它仅适用于字符串,并且像其他任何手动格式一样,它本质上是可选的,非强制性的措施,容易出现任何类型的人为错误。

我认为所有这些都是由于一种非常古老的迷信,得到了诸如 OWASP PHP手册,该手册宣称无论在"转义"和防止SQL注入之间都是平等的。

无论PHP手册使用了多长时间, *_escape_string都不会使数据安全,而且从未打算这样做。除了对于字符串以外的任何SQL部分都没有用,手动转义是错误的,因为它是手动的,与自动的相反。

OWASP使情况变得更糟,强调转义用户输入这完全是胡说:在注入保护的上下文中不应有这样的用语。每个变量都有潜在的危险-无论来源如何!或者,换句话说-每个变量都必须正确设置格式才能放入查询中-不管源是什么。重要的是目的地。当开发人员开始将绵羊与山羊分开时(考虑某些特定变量是否"安全"),他/她迈出了走向灾难的第一步。更不用说即使是措辞也建议在入口点进行大量转义,类似于非常讨厌的引号功能-已被轻视,不推荐使用和删除。

因此,与任何"转义"不同,准备好的语句一种确实可以防止SQL注入的措施(适用时)。

A3:

Every answer here covers only part of the problem. In fact, there are four different query parts which we can add to SQL dynamically: -

  • a string
  • a number
  • an identifier
  • a syntax keyword

And prepared statements cover only two of them.

But sometimes we have to make our query even more dynamic, adding operators or identifiers as well. So, we will need different protection techniques.

In general, such a protection approach is based on whitelisting.

In this case, every dynamic parameter should be hardcoded in your script and chosen from that set. For example, to do dynamic ordering:

$orders  = array("name", "price", "qty"); // Field names
$key = array_search($_GET['sort'], $orders)); // if we have such a name
$orderby = $orders[$key]; // If not, first one will be set automatically. 
$query = "SELECT * FROM `table` ORDER BY $orderby"; // Value is safe

To ease the process I wrote a whitelist helper function that does all the job in one line:

$orderby = white_list($_GET['orderby'], "name", ["name","price","qty"], "Invalid field name");
$query  = "SELECT * FROM `table` ORDER BY `$orderby`"; // sound and safe

There is another way to secure identifiers - escaping but I rather stick to whitelisting as a more robust and explicit approach. Yet as long as you have an identifier quoted, you can escape the quote character to make it safe. For example, by default for mysql you have to double the quote character to escape it. For other other DBMS escaping rules would be different.

Still, there is an issue with SQL syntax keywords (such as AND, DESC and such), but white-listing seems the only approach in this case.

So, a general recommendation may be phrased as

  • Any variable that represents an SQL data literal, (or, to put it simply - an SQL string, or a number) must be added through a prepared statement. No Exceptions.
  • Any other query part, such as an SQL keyword, a table or a field name, or an operator - must be filtered through a white list.

Update

Although there is a general agreement on the best practices regarding SQL injection protection, there are still many bad practices as well. And some of them too deeply rooted in the minds of PHP users. For instance, on this very page there are (although invisible to most visitors) more than 80 deleted answers - all removed by the community due to bad quality or promoting bad and outdated practices. Worse yet, some of the bad answers aren't deleted, but rather prospering.

For example, there(1) are(2) still(3) many(4) answers(5), including the second most upvoted answer suggesting you manual string escaping - an outdated approach that is proven to be insecure.

Or there is a slightly better answer that suggests just another method of string formatting and even boasts it as the ultimate panacea. While of course, it is not. This method is no better than regular string formatting, yet it keeps all its drawbacks: it is applicable to strings only and, like any other manual formatting, it's essentially optional, non-obligatory measure, prone to human error of any sort.

I think that all this because of one very old superstition, supported by such authorities like OWASP or the PHP manual, which proclaims equality between whatever "escaping" and protection from SQL injections.

Regardless of what PHP manual said for ages, *_escape_string by no means makes data safe and never has been intended to. Besides being useless for any SQL part other than string, manual escaping is wrong, because it is manual as opposite to automated.

And OWASP makes it even worse, stressing on escaping user input which is an utter nonsense: there should be no such words in the context of injection protection. Every variable is potentially dangerous - no matter the source! Or, in other words - every variable has to be properly formatted to be put into a query - no matter the source again. It's the destination that matters. The moment a developer starts to separate the sheep from the goats (thinking whether some particular variable is "safe" or not) he/she takes his/her first step towards disaster. Not to mention that even the wording suggests bulk escaping at the entry point, resembling the very magic quotes feature - already despised, deprecated and removed.

So, unlike whatever "escaping", prepared statements is the measure that indeed protects from SQL injection (when applicable).

回答4:

我建议使用 PDO (PHP数据对象)来运行参数化的SQL查询。

这不仅可以防止SQL注入,还可以加快查询速度。

通过使用PDO而不是mysql_mysqli_pgsql_函数,可以使应用程序从数据库中抽象一些,在极少数情况下,您必须切换数据库提供程序。

A4:

I'd recommend using PDO (PHP Data Objects) to run parameterized SQL queries.

Not only does this protect against SQL injection, but it also speeds up queries.

And by using PDO rather than mysql_, mysqli_, and pgsql_ functions, you make your application a little more abstracted from the database, in the rare occurrence that you have to switch database providers.

回答5:

使用PDO并准备好查询。

($connPDO对象)

$stmt = $conn->prepare("INSERT INTO tbl VALUES(:id, :name)");
$stmt->bindValue(':id', $id);
$stmt->bindValue(':name', $name);
$stmt->execute();

A5:

Use PDO and prepared queries.

($conn is a PDO object)

$stmt = $conn->prepare("INSERT INTO tbl VALUES(:id, :name)");
$stmt->bindValue(':id', $id);
$stmt->bindValue(':name', $name);
$stmt->execute();

回答6:

如您所见,人们建议您最多使用准备好的语句。没错,但是当您对每个进程只执行一次一次查询时,就会有轻微的性能损失。

我正面临这个问题,但我认为我以非常复杂的方式解决了该问题-黑客用来避免使用引号的方式。我将其与模拟的准备好的语句结合使用。我用它来防止所有种可能的SQL注入攻击。

我的方法:

  • 如果您希望输入为整数,请确保它为 真正 整数。在像PHP这样的可变类型语言中,这非常重要。例如,您可以使用这种非常简单但功能强大的解决方案:sprintf("SELECT1,2,3FROMtableWHERE4=%u",$input);

  • 如果您希望整数十六进制之外还有其他内容。如果您将其十六进制化,则可以完全避免所有输入。在C / C ++中,有一个名为 mysql_hex_string() ,在PHP中,您可以使用 bin2hex()

    不必担心转义的字符串的大小是其原始长度的2倍,因为即使您使用mysql_real_escape_string, PHP必须分配相同的容量(((2*input_length)+1),这是相同的。

  • 当您传输二进制数据时,经常使用此十六进制方法,但是我认为没有理由不对所有数据使用它来防止SQL注入攻击。请注意,您必须在数据前添加0x或使用MySQL函数UNHEX

例如,查询:

SELECT password FROM users WHERE name = 'root'

将成为:

SELECT password FROM users WHERE name = 0x726f6f74

SELECT password FROM users WHERE name = UNHEX('726f6f74')

十六进制是完美的逃脱。无法注入。

UNHEX函数与0x前缀之间的区别

在评论中进行了一些讨论,所以我最后想澄清一下。这两种方法非常相似,但是在某些方面有所不同:

** 0x **前缀只能用于数据列,例如 char,varchar,text,block,binary等
此外,如果您将要插入一个空字符串。您必须将其完全替换为'',否则会出现错误。

UNHEX()可用于 any 列;您不必担心空字符串。


十六进制方法通常被用作攻击

请注意,此十六进制方法通常用作SQL注入攻击,其中整数就像字符串,并且仅通过mysql_real_escape_string进行转义。这样就可以避免使用引号。

例如,如果您只是执行以下操作:

"SELECT title FROM article WHERE id = " . mysql_real_escape_string($_GET["id"])

攻击可以非常轻松地给您注入。考虑从脚本返回的以下注入代码:

SELECT ... WHERE id = -1并全部从information_schema.tables中选择table_name

现在只提取表结构:

SELECT ... WHERE id = -1并全部从information_schema.column中选择column_name,其中table_name = 0x61727469636c65

然后选择所需的任何数据。是不是很酷?

但是,如果可注入站点的编码器将其十六进制化,则不可能进行注入,因为查询看起来像这样:SELECT...WHEREid=UNHEX('2d312075...3635')

A6:

As you can see, people suggest you use prepared statements at the most. It's not wrong, but when your query is executed just once per process, there would be a slight performance penalty.

I was facing this issue, but I think I solved it in very sophisticated way - the way hackers use to avoid using quotes. I used this in conjunction with emulated prepared statements. I use it to prevent all kinds of possible SQL injection attacks.

My approach:

  • If you expect input to be integer make sure it's really integer. In a variable-type language like PHP it is this very important. You can use for example this very simple but powerful solution: sprintf("SELECT 1,2,3 FROM table WHERE 4 = %u", $input);

  • If you expect anything else from integer hex it. If you hex it, you will perfectly escape all input. In C/C++ there's a function called mysql_hex_string(), in PHP you can use bin2hex().

    Don't worry about that the escaped string will have a 2x size of its original length because even if you use mysql_real_escape_string, PHP has to allocate same capacity ((2*input_length)+1), which is the same.

  • This hex method is often used when you transfer binary data, but I see no reason why not use it on all data to prevent SQL injection attacks. Note that you have to prepend data with 0x or use the MySQL function UNHEX instead.

So, for example, the query:

SELECT password FROM users WHERE name = 'root'

Will become:

SELECT password FROM users WHERE name = 0x726f6f74

or

SELECT password FROM users WHERE name = UNHEX('726f6f74')

Hex is the perfect escape. No way to inject.

Difference between UNHEX function and 0x prefix

There was some discussion in comments, so I finally want to make it clear. These two approaches are very similar, but they are a little different in some ways:

The ** 0x** prefix can only be used for data columns such as char, varchar, text, block, binary, etc.
Also, its use is a little complicated if you are about to insert an empty string. You'll have to entirely replace it with '', or you'll get an error.

UNHEX() works on any column; you do not have to worry about the empty string.


Hex methods are often used as attacks

Note that this hex method is often used as an SQL injection attack where integers are just like strings and escaped just with mysql_real_escape_string. Then you can avoid the use of quotes.

For example, if you just do something like this:

"SELECT title FROM article WHERE id = " . mysql_real_escape_string($_GET["id"])

an attack can inject you very easily. Consider the following injected code returned from your script:

SELECT ... WHERE id = -1 union all select table_name from information_schema.tables

and now just extract table structure:

SELECT ... WHERE id = -1 union all select column_name from information_schema.column where table_name = 0x61727469636c65

And then just select whatever data ones want. Isn't it cool?

But if the coder of an injectable site would hex it, no injection would be possible because the query would look like this: SELECT ... WHERE id = UNHEX('2d312075...3635')

回答7:

不建议使用的警告::该答案的示例代码(如问题的示例代码)使用PHP的MySQL扩展名,该扩展名在PHP 5.5.0中已弃用,并在PHP 7.0中被完全删除.0。

安全警告:此答案与安全最佳做法不符。 转义不足以防止SQL注入,请使用准备好的语句。使用以下概述的策略需要您自担风险。 (此外,在PHP 7中删除了mysql_real_escape_string()。)

重要

防止SQL注入的最佳方法是使用 Prepared Statements (而不是转义)代替已接受的答案说明。

有诸如 Aura.Sql EasyDB ,使开发人员可以更轻松地使用准备好的语句。要了解有关为何准备好的语句更好的信息,请参见停止SQL注入,请参考mysql_real_escape_string()绕过最近在WordPress中修复了Unicode SQL注入漏洞

防止注入- mysql_real_escape_string()

PHP具有防止这些攻击的特制功能。您所需要做的就是使用大量函数mysql_real_escape_string

mysql_real_escape_string接受将在MySQL查询中使用的字符串,并返回相同的字符串,并安全地转义了所有SQL注入尝试。基本上,它将用MySQL安全的替代品(转义的引号\')代替用户可能输入的那些麻烦的引号(')。

注意::您必须连接到数据库才能使用此功能!

//连接到MySQL

$name_bad = "' OR 1'"; 

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";


$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 

$name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection: <br />" . $query_evil;

您可以在 MySQL-SQL注入预防中找到更多详细信息

A7:

Deprecated Warning: This answer's sample code (like the question's sample code) uses PHP's MySQL extension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.

Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also, mysql_real_escape_string() was removed in PHP 7.)

IMPORTANT

The best way to prevent SQL Injection is to use Prepared Statements instead of escaping, as the accepted answer demonstrates.

There are libraries such as Aura.Sql and EasyDB that allow developers to use prepared statements easier. To learn more about why prepared statements are better at stopping SQL injection, refer to this mysql_real_escape_string() bypass and recently fixed Unicode SQL Injection vulnerabilities in WordPress.

Injection prevention - mysql_real_escape_string()

PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function, mysql_real_escape_string.

mysql_real_escape_string takes a string that is going to be used in a MySQL query and return the same string with all SQL injection attempts safely escaped. Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.

NOTE: you must be connected to the database to use this function!

// Connect to MySQL

$name_bad = "' OR 1'"; 

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";


$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 

$name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection: <br />" . $query_evil;

You can find more details in MySQL - SQL Injection Prevention.

回答8:

您可以执行以下基本操作:

$safe_variable = mysqli_real_escape_string($_POST["user-input"], $dbConnection);
mysqli_query($dbConnection, "INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

这不会解决所有问题,但这是一个很好的垫脚石。我省略了一些明显的项目,例如检查变量的存在,格式(数字,字母等)。

A8:

You could do something basic like this:

$safe_variable = mysqli_real_escape_string($_POST["user-input"], $dbConnection);
mysqli_query($dbConnection, "INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

This won't solve every problem, but it's a very good stepping stone. I left out obvious items such as checking the variable's existence, format (numbers, letters, etc.).

回答9:

无论您最终使用什么,请确保您尚未检查输入内容是否已被magic_quotes或其他一些好听的垃圾所破坏,并在必要时通过除斜杠或其他可以对其进行消毒的

A9:

Whatever you do end up using, make sure that you check your input hasn't already been mangled by magic_quotes or some other well-meaning rubbish, and if necessary, run it through stripslashes or whatever to sanitize it.

回答10:

不建议使用的警告::该答案的示例代码(如问题的示例代码)使用PHP的MySQL扩展名,该扩展名在PHP 5.5.0中已弃用,并在PHP 7.0中被完全删除.0。

安全警告:此答案与安全最佳做法不符。 转义不足以防止SQL注入,请使用准备好的语句。使用以下概述的策略需要您自担风险。 (此外,在PHP 7中删除了mysql_real_escape_string()。)

参数化查询和输入验证是必经之路。即使使用了mysql_real_escape_string(),在很多情况下也可能发生SQL注入。

这些示例容易受到SQL注入的攻击:

$offset = isset($_GET['o']) ? $_GET['o'] : 0;
$offset = mysql_real_escape_string($offset);
RunQuery("SELECT userid, username FROM sql_injection_test LIMIT $offset, 10");

$order = isset($_GET['o']) ? $_GET['o'] : 'userid';
$order = mysql_real_escape_string($order);
RunQuery("SELECT userid, username FROM sql_injection_test ORDER BY `$order`");

在两种情况下,您都不能使用'保护封装。

来源意外的SQL注入(当不进行转义时)足够)

A10:

Deprecated Warning: This answer's sample code (like the question's sample code) uses PHP's MySQL extension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.

Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also, mysql_real_escape_string() was removed in PHP 7.)

Parameterized query AND input validation is the way to go. There are many scenarios under which SQL injection may occur, even though mysql_real_escape_string() has been used.

Those examples are vulnerable to SQL injection:

$offset = isset($_GET['o']) ? $_GET['o'] : 0;
$offset = mysql_real_escape_string($offset);
RunQuery("SELECT userid, username FROM sql_injection_test LIMIT $offset, 10");

or

$order = isset($_GET['o']) ? $_GET['o'] : 'userid';
$order = mysql_real_escape_string($order);
RunQuery("SELECT userid, username FROM sql_injection_test ORDER BY `$order`");

In both cases, you can't use ' to protect the encapsulation.

Source: The Unexpected SQL Injection (When Escaping Is Not Enough)

回答11:

我认为,通常防止在PHP应用程序(或任何Web应用程序)中进行SQL注入的最佳方法是考虑应用程序的体系结构。如果防止SQL注入的唯一方法是记住每次使用数据库时都使用一种执行"正确的事情"的特殊方法或函数,那么您做错了。这样,在代码中的某个时刻忘记正确格式化查询只是一个时间问题。

采用MVC模式和类似 CakePHP CodeIgniter 可能是正确的方法:在这样的框架中,常见的任务(如创建安全的数据库查询)已经解决并集中实现。它们可帮助您以明智的方式组织Web应用程序,并使您更多地考虑加载和保存对象,而不是安全地构造单个SQL查询。

A11:

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, for that matter) is to think about your application's architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it's just a matter of time until you forget to correctly format your query at some point in your code.

Adopting the MVC pattern and a framework like CakePHP or CodeIgniter is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

回答12:

我赞成存储过程(自5.0以来,MySQL一直支持存储过程)-从安全角度来看-优点是-

  1. 大多数数据库(包括 MySQL )使用户访问仅限于执行存储过程。细粒度的安全访问控制对于防止特权攻击升级很有用。这样可以防止受感染的应用程序直接对数据库运行SQL。
  2. 它们从应用程序中提取原始SQL查询,因此应用程序可使用的数据库结构信息较少。这使得人们更难理解数据库的底层结构并设计适当的攻击。<​​/ li>
  3. 它们仅接受参数,因此存在参数化查询的优点。当然-IMO您仍然需要清理输入-特别是如果您在存储过程中使用动态SQL。

缺点是-

  1. 它们(存储过程)难以维护,并且繁殖非常快。这使得管理它们成为一个问题。
  2. 它们不是非常适合动态查询-如果它们被构建为接受动态代码作为参数,那么很多优点就被抵消了。

A12:

I favor stored procedures (MySQL has had stored procedures support since 5.0) from a security point of view - the advantages are -

  1. Most databases (including MySQL) enable user access to be restricted to executing stored procedures. The fine-grained security access control is useful to prevent escalation of privileges attacks. This prevents compromised applications from being able to run SQL directly against the database.
  2. They abstract the raw SQL query from the application so less information of the database structure is available to the application. This makes it harder for people to understand the underlying structure of the database and design suitable attacks.
  3. They accept only parameters, so the advantages of parameterized queries are there. Of course - IMO you still need to sanitize your input - especially if you are using dynamic SQL inside the stored procedure.

The disadvantages are -

  1. They (stored procedures) are tough to maintain and tend to multiply very quickly. This makes managing them an issue.
  2. They are not very suitable for dynamic queries - if they are built to accept dynamic code as parameters then a lot of the advantages are negated.

回答13:

有很多方法可以防止SQL注入和其他SQL hack。您可以在Internet(Google搜索)上轻松找到它。当然, PDO是很好的解决方案之一。但是我想向您推荐一些防止SQL注入的良好链接预防措施。

什么是SQL注入以及如何防止

用于SQL注入的PHP手册

Microsoft对PHP中SQL注入和预防的解释

以及其他类似 防止SQL MySQL和PHP注入

现在,为什么需要阻止SQL注入查询?

我想告诉您:为什么我们尝试通过下面的简短示例来防止SQL注入:

查询登录身份验证匹配项:

$query="select * from users where email='".$_POST['email']."' and password='".$_POST['password']."' ";

现在,如果有人(黑客)放了

$_POST['email']= admin@emali.com' OR '1=1

并输入任何密码......

查询将仅在以下情况下解析到系统中:

$query="select * from users where email='admin@emali.com' OR '1=1';

另一部分将被丢弃。那么,会发生什么呢?未经授权的用户(黑客)将能够以管理员身份登录,而无需输入密码。现在,他/她可以执行管理员/电子邮件人员可以执行的任何操作。瞧,如果不阻止SQL注入是非常危险的。

A13:

There are many ways of preventing SQL injections and other SQL hacks. You can easily find it on the Internet (Google Search). Of course PDO is one of the good solutions. But I would like to suggest you some good links prevention from SQL injection.

What is SQL injection and how to prevent

PHP manual for SQL injection

Microsoft explanation of SQL injection and prevention in PHP

And some other like Preventing SQL injection with MySQL and PHP.

Now, why you do you need to prevent your query from SQL injection?

I would like to let you know: Why do we try for preventing SQL injection with a short example below:

Query for login authentication match:

$query="select * from users where email='".$_POST['email']."' and password='".$_POST['password']."' ";

Now, if someone (a hacker) puts

$_POST['email']= admin@emali.com' OR '1=1

and password anything....

The query will be parsed into the system only up to:

$query="select * from users where email='admin@emali.com' OR '1=1';

The other part will be discarded. So, what will happen? A non-authorized user (hacker) will be able to log in as administrator without having his/her password. Now, he/she can do anything that the administrator/email person can do. See, it's very dangerous if SQL injection is not prevented.

回答14:

我想如果有人想使用PHP和MySQL或其他一些数据库服务器:

  1. 考虑学习 PDO (PHP数据对象)–它是提供统一的数据库访问层访问多个数据库的方法。
  2. 考虑学习 MySQLi
  3. 使用本机PHP函数,例如: strip_tags mysql_real_escape_string 或如果是可变数字,则只需(int)$foo。在PHP的此处中详细了解变量类型。如果您使用的是PDO或MySQLi之类的库,请始终使用 PDO :: quote()和<一个href=" http: php.net manual en mysqli.real-escape-string.php"> mysqli_real_escape_string()。

库示例:

---- PDO

-----没有占位符-SQL注入已经成熟! 不好

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values ($name, $addr, $city)");

-----未命名的占位符

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values (?, ?, ?);

-----命名占位符

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) value (:name, :addr, :city)");

--- MySQLi

$request = $mysqliConnection->prepare('
       SELECT * FROM trainers
       WHERE name = ?
       AND email = ?
       AND last_login > ?');

    $query->bind_param('first_param', 'second_param', $mail, time() - 3600);
    $query->execute();

PS

PDO轻松赢得了这场战斗。通过支持十二种不同的数据库驱动程序和命名参数,我们可以忽略微小的性能损失,并习惯其API。从安全的角度来看,只要开发人员以应有的方式使用它们,两者都是安全的。

但是,尽管PDO和MySQLi都非常快,但MySQLi在基准测试中的执行速度却微不足道-未准备好的语句约为2.5%,而准备好的语句约为6.5%。

并且请测试对数据库的每个查询-这是防止注入的更好方法。

A14:

I think if someone wants to use PHP and MySQL or some other dataBase server:

  1. Think about learning PDO (PHP Data Objects) – it is a database access layer providing a uniform method of access to multiple databases.
  2. Think about learning MySQLi
  3. Use native PHP functions like: strip_tags, mysql_real_escape_string or if variable numeric, just (int)$foo. Read more about type of variables in PHP here. If you're using libraries such as PDO or MySQLi, always use PDO::quote() and mysqli_real_escape_string().

Libraries examples:

---- PDO

----- No placeholders - ripe for SQL injection! It's bad

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values ($name, $addr, $city)");

----- Unnamed placeholders

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values (?, ?, ?);

----- Named placeholders

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) value (:name, :addr, :city)");

--- MySQLi

$request = $mysqliConnection->prepare('
       SELECT * FROM trainers
       WHERE name = ?
       AND email = ?
       AND last_login > ?');

    $query->bind_param('first_param', 'second_param', $mail, time() - 3600);
    $query->execute();

P.S:

PDO wins this battle with ease. With support for twelve different database drivers and named parameters, we can ignore the small performance loss, and get used to its API. From a security standpoint, both of them are safe as long as the developer uses them the way they are supposed to be used

But while both PDO and MySQLi are quite fast, MySQLi performs insignificantly faster in benchmarks – ~2.5% for non-prepared statements, and ~6.5% for prepared ones.

And please test every query to your database - it's a better way to prevent injection.

回答15:

如果可能,强制转换参数的类型。但这仅适用于int,bool和float等简单类型。

$unsafe_variable = $_POST['user_id'];

$safe_variable = (int)$unsafe_variable ;

mysqli_query($conn, "INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

A15:

If possible, cast the types of your parameters. But it's only working on simple types like int, bool, and float.

$unsafe_variable = $_POST['user_id'];

$safe_variable = (int)$unsafe_variable ;

mysqli_query($conn, "INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

回答16:

如果您想利用缓存引擎,例如 Redis 或< a href=" http://en.wikipedia.org/wiki/Memcached" rel="noreferrer"> Memcached ,也许DALMP是一个选择。它使用纯 MySQLi 。检查以下内容:使用PHP的MySQL DALMP数据库抽象层。

此外,您可以在准备查询之前"准备"参数,以便构建动态查询,最后进行充分准备的语句查询。 使用PHP的MySQL DALMP数据库抽象层。

A16:

If you want to take advantage of cache engines, like Redis or Memcached, maybe DALMP could be a choice. It uses pure MySQLi. Check this: DALMP Database Abstraction Layer for MySQL using PHP.

Also, you can 'prepare' your arguments before preparing your query so that you can build dynamic queries and at the end have a fully prepared statements query. DALMP Database Abstraction Layer for MySQL using PHP.

回答17:

对于那些不确定如何使用PDO(来自mysql_函数)的人,我做了一个非常非常简单的PDO包装器,它是一个文件。它的存在表明执行应用程序需要完成的所有普通操作是多么容易。可与PostgreSQL,MySQL和SQLite一起使用。

基本上,请在阅读手册的同时阅读 ,以了解如何在现实生活中使用PDO功能使您可以轻松地以所需的格式 来存储和检索值。

我要一列

$count = DB::column('SELECT COUNT(*) FROM `user`);

我想要一个数组(键=>值)结果(即用于创建选择框)

$pairs = DB::pairs('SELECT `id`, `username` FROM `user`);

我想要单行结果

$user = DB::row('SELECT * FROM `user` WHERE `id` = ?', array($user_id));

我想要一系列结果

$banned_users = DB::fetch('SELECT * FROM `user` WHERE `banned` = ?', array(TRUE));

A17:

For those unsure of how to use PDO (coming from the mysql_ functions), I made a very, very simple PDO wrapper that is a single file. It exists to show how easy it is to do all the common things applications need to be done. Works with PostgreSQL, MySQL, and SQLite.

Basically, read it while you read the manual to see how to put the PDO functions to use in real life to make it simple to store and retrieve values in the format you want.

I want a single column

$count = DB::column('SELECT COUNT(*) FROM `user`);

I want an array(key => value) results (i.e. for making a selectbox)

$pairs = DB::pairs('SELECT `id`, `username` FROM `user`);

I want a single row result

$user = DB::row('SELECT * FROM `user` WHERE `id` = ?', array($user_id));

I want an array of results

$banned_users = DB::fetch('SELECT * FROM `user` WHERE `banned` = ?', array(TRUE));

回答18:

使用此PHP函数mysql_escape_string(),您可以快速获得很好的预防效果。

例如:

SELECT * FROM users WHERE name = '".mysql_escape_string($name_from_html_form)."'

mysql_escape_string —转义用于mysql_query的字符串

为进一步预防,您可以在末尾添加...

wHERE 1=1   or  LIMIT 1

最后,您得到:

SELECT * FROM users WHERE name = '".mysql_escape_string($name_from_html_form)."' LIMIT 1

A18:

Using this PHP function mysql_escape_string() you can get a good prevention in a fast way.

For example:

SELECT * FROM users WHERE name = '".mysql_escape_string($name_from_html_form)."'

mysql_escape_string — Escapes a string for use in a mysql_query

For more prevention, you can add at the end ...

wHERE 1=1   or  LIMIT 1

Finally you get:

SELECT * FROM users WHERE name = '".mysql_escape_string($name_from_html_form)."' LIMIT 1

回答19:

一些在SQL语句中转义特殊字符的准则。

请勿使用 MySQL 。此扩展名已弃用。使用 MySQLi PDO

MySQLi

要在字符串中手动转义特殊字符,可以使用 mysqli_real_escape_string 函数。除非使用 mysqli_set_charset

示例:

$mysqli = new mysqli('host', 'user', 'password', 'database');
$mysqli->set_charset('charset');

$string = $mysqli->real_escape_string($string);
$mysqli->query("INSERT INTO table (column) VALUES ('$string')");

要使用准备好的语句自动转义值,请使用 mysqli_prepare ,和 mysqli_stmt_bind_param ,其中对应绑定变量的类型必须为提供了适当的转换:

示例:

$stmt = $mysqli->prepare("INSERT INTO table (column1, column2) VALUES (?,?)");

$stmt->bind_param("is", $integer, $string);

$stmt->execute();

无论您使用准备好的语句还是mysqli_real_escape_string,您始终必须了解要使用的输入数据的类型。

因此,如果使用准备好的语句,则必须为mysqli_stmt_bind_param函数指定变量的类型。

顾名思义,使用mysqli_real_escape_string是为了在字符串中转义特殊字符,因此不会使整数安全。此功能的目的是防止破坏SQL语句中的字符串以及可能导致的数据库损坏。正确使用mysqli_real_escape_string是一个有用的函数,尤其是与sprintf结合使用时。

示例:

$string = "x' OR name LIKE '%John%";
$integer = '5 OR id != 0';

$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string($string), $integer);

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 5

$integer = '99999999999999999999';
$query = sprintf("SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string($string), $integer);

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 2147483647

A19:

A few guidelines for escaping special characters in SQL statements.

Don't use MySQL. This extension is deprecated. Use MySQLi or PDO instead.

MySQLi

For manually escaping special characters in a string you can use the mysqli_real_escape_string function. The function will not work properly unless the correct character set is set with mysqli_set_charset.

Example:

$mysqli = new mysqli('host', 'user', 'password', 'database');
$mysqli->set_charset('charset');

$string = $mysqli->real_escape_string($string);
$mysqli->query("INSERT INTO table (column) VALUES ('$string')");

For automatic escaping of values with prepared statements, use mysqli_prepare, and mysqli_stmt_bind_param where types for the corresponding bind variables must be provided for an appropriate conversion:

Example:

$stmt = $mysqli->prepare("INSERT INTO table (column1, column2) VALUES (?,?)");

$stmt->bind_param("is", $integer, $string);

$stmt->execute();

No matter if you use prepared statements or mysqli_real_escape_string, you always have to know the type of input data you're working with.

So if you use a prepared statement, you must specify the types of the variables for mysqli_stmt_bind_param function.

And the use of mysqli_real_escape_string is for, as the name says, escaping special characters in a string, so it will not make integers safe. The purpose of this function is to prevent breaking the strings in SQL statements, and the damage to the database that it could cause. mysqli_real_escape_string is a useful function when used properly, especially when combined with sprintf.

Example:

$string = "x' OR name LIKE '%John%";
$integer = '5 OR id != 0';

$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string($string), $integer);

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 5

$integer = '99999999999999999999';
$query = sprintf("SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string($string), $integer);

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 2147483647

回答20:

可以通过在数据库本身中授予适当的权限来解决此问题的简单替代方案。例如:如果您使用的是MySQL数据库,则通过终端或提供的UI进入数据库,只需遵循以下命令:

 GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';

这将限制用户只限于指定的查询。删除删除权限,使数据永远不会从PHP页面触发的查询中删除。第二件事是刷新权限,以便MySQL刷新权限并更新。

FLUSH PRIVILEGES; 

有关刷新的更多信息。

要查看用户的当前特权,请执行以下查询。

select * from mysql.user where User='username';

详细了解 GRANT

A20:

The simple alternative to this problem could be solved by granting appropriate permissions in the database itself. For example: if you are using a MySQL database then enter into the database through terminal or the UI provided and just follow this command:

 GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';

This will restrict the user to only get confined with the specified query's only. Remove the delete permission and so the data would never get deleted from the query fired from the PHP page. The second thing to do is to flush the privileges so that the MySQL refreshes the permissions and updates.

FLUSH PRIVILEGES; 

more information about flush.

To see the current privileges for the user fire the following query.

select * from mysql.user where User='username';

Learn more about GRANT.

回答21:

关于许多有用的答案,我希望为该线程增加一些价值。

SQL注入是一种可以通过用户输入(由用户填充然后在查询中使用的输入)进行的攻击。 SQL注入模式是正确的查询语法,我们可以称之为:错误查询是出于错误的原因,并且我们假设可能有一个错误的人试图获取影响安全性三原则(机密性)的秘密信息(绕过访问控制)。 ,完整性和可用性)。

现在,我们的观点是要防止诸如SQL注入攻击之类的安全威胁,这个问题要问(如何使用PHP防止SQL注入攻击),更现实一些,使用用户-在这种查询中不能使用PHP或任何其他编程语言输入数据,或者不是像更多人建议的那样使用现代技术(例如,准备好的语句或当前支持SQL注入预防的任何其他工具),请认为这些工具不再可用?您如何保护您的应用程序?

我反对SQL注入的方法是:在将用户输入的数据发送到数据库之前(在任何查询中使用它之前),将其清除。

数据过滤(将不安全数据转换为安全数据)

请考虑 PDO MySQLi 不可用。您如何保护您的应用程序?你强迫我使用它们吗?除了PHP以外的其他语言呢?我倾向于提供一般性的想法,因为它不仅可以用于特定的语言,而且可以用于更广泛的边界。

  1. SQL用户(限制用户特权):最常见的SQL操作是(SELECT,UPDATE,INSERT),那么,为什么将UPDATE特权赋予不需要的用户呢?例如,登录和搜索页面仅使用SELECT,那么,为什么在这些页面中以高特权使用DB用户?

规则:不要为所有特权创建一个数据库用户。对于所有SQL操作,您都可以创建方案(例如(deluser,selectuser,updateuser))作为用户名,以便于使用。

请参见最低特权原则

  1. 数据过滤:在构建任何查询用户输入之前,应先对其进行验证和过滤。对于程序员而言,为每个用户输入变量定义一些属性很重要:数据类型,数据模式和数据长度。 (x和y)之间的数字的字段必须使用确切的规则进行严格验证,对于字符串(文本)的字段,则必须使用模式:pattern是这种情况,例如,用户名只能包含一些字符,让我们说[a-zA-Z0-9_-。]。长度在(x和n)之间变化,其中x和n(整数,x <= n)。 规则:创建精确的过滤器和验证规则对我来说是最佳做法。

  2. 使用其他工具:在这里,我也将与您同意准备好的语句(参数化查询)和存储过程。这里的缺点是这些方式需要大多数用户不具备的高级技能。这里的基本思想是区分SQL查询和内部使用的数据。这两种方法甚至都可以用于不安全的数据,因为此处的用户输入数据不会向原始查询添加任何内容,例如(any或x = x)。

有关更多信息,请阅读 OWASP SQL预防注入速查表

现在,如果您是高级用户,请根据需要开始使用此防御,但是,对于初学者来说,如果他们不能快速实现存储过程并准备语句,那么最好尽可能过滤输入数据

最后,让我们考虑一个用户在下面发送此文本而不是输入他/她的用户名:

[1] UNION SELECT IF(SUBSTRING(Password,1,1)='2',BENCHMARK(100000,SHA1(1)),0) User,Password FROM mysql.user WHERE User = 'root'

可以在没有任何准备好的语句和存储过程的情况下及早检查此输入,但是为了安全起见,请在用户数据过滤和验证之后开始使用它们。

最后一点是检测意外行为,这需要更多的精力和复杂性;不建议在普通的Web应用程序中使用。

上述用户输入中的意外行为是SELECT,UNION,IF,SUBSTRING,BENCHMARK,SHA和root。一旦检测到这些单词,就可以避免输入。

更新1:

一个用户评论说这篇文章没用,好!这是提供的OWASP.ORG

主要防御措施:

选项1:使用预处理语句(参数化查询)
选项2:使用存储过程
选项3:转义所有用户提供的输入

其他防御措施:

同时执行:最低特权
还执行:白名单输入验证

您可能知道,主张某篇文章应有一个有效的论点支持,至少要有一个引用!否则,将其视为攻击和不当索赔!

更新2:

在PHP手册中, PHP:预准备语句-手册

转义和SQL注入

绑定变量将由服务器自动转义。服务器在执行之前将其转义的值插入语句模板的适当位置。必须向服务器提供有关绑定变量类型的提示,以创建适当的转换。有关更多信息,请参见mysqli_stmt_bind_param()函数。

服务器内部值的自动转义有时被认为是防止SQL注入的安全功能。如果正确地转义了输入值,则使用非准备的语句可以达到相同的安全性。

更新3:

我创建了测试用例,以了解在使用准备好的语句时PDO和MySQLi如何将查询发送到MySQL服务器:

PDO:

$user = "''1''"; // Malicious keyword
$sql = 'SELECT * FROM awa_user WHERE userame =:username';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':username' => $user));

查询日志:

    189 Query SELECT * FROM awa_user WHERE userame ='\'\'1\'\''
    189 Quit

MySQLi:

$stmt = $mysqli->prepare("SELECT * FROM awa_user WHERE username =?")) {
$stmt->bind_param("s", $user);
$user = "''1''";
$stmt->execute();

查询日志:

    188 Prepare   SELECT * FROM awa_user WHERE username =?
    188 Execute   SELECT * FROM awa_user WHERE username ='\'\'1\'\''
    188 Quit

很明显,一条准备好的语句也在转义数据,没有别的。

如上所述,

服务器内部值的自动转义有时被认为是防止SQL注入的安全功能。如果正确地对输入值进行转义,则使用未准备的语句可以达到相同的安全性

因此,这证明了在发送任何查询之前,数据验证(例如intval())对于整数值是个好主意。另外,一种正确有效的方法是在发送查询之前防止恶意用户数据。

请查看此问题以获取更多详细信息: PDO将原始查询发送到MySQL,而Mysqli发送已准备好的查询,两者都产生相同的结果

参考文献:

  1. SQL注入备忘单
  2. SQL注入
  3. 信息安全
  4. 安全原则
  5. 数据验证

A21:

Regarding many useful answers, I hope to add some value to this thread.

SQL injection is an attack that can be done through user inputs (inputs that filled by a user and then used inside queries). The SQL injection patterns are correct query syntax while we can call it: bad queries for bad reasons, and we assume that there might be a bad person that try to get secret information (bypassing access control) that affect the three principles of security (confidentiality, integrity, and availability).

Now, our point is to prevent security threats such as SQL injection attacks, the question asking (how to prevent an SQL injection attack using PHP), be more realistic, data filtering or clearing input data is the case when using user-input data inside such query, using PHP or any other programming language is not the case, or as recommended by more people to use modern technology such as prepared statement or any other tools that currently supporting SQL injection prevention, consider that these tools not available anymore? How do you secure your application?

My approach against SQL injection is: clearing user-input data before sending it to the database (before using it inside any query).

Data filtering for (converting unsafe data to safe data)

Consider that PDO and MySQLi are not available. How can you secure your application? Do you force me to use them? What about other languages other than PHP? I prefer to provide general ideas as it can be used for wider border, not just for a specific language.

  1. SQL user (limiting user privilege): most common SQL operations are (SELECT, UPDATE, INSERT), then, why give the UPDATE privilege to a user that does not require it? For example, login, and search pages are only using SELECT, then, why use DB users in these pages with high privileges?

RULE: do not create one database user for all privileges. For all SQL operations, you can create your scheme like (deluser, selectuser, updateuser) as usernames for easy usage.

See principle of least privilege.

  1. Data filtering: before building any query user input, it should be validated and filtered. For programmers, it's important to define some properties for each user-input variables: data type, data pattern, and data length. A field that is a number between (x and y) must be exactly validated using the exact rule, and for a field that is a string (text): pattern is the case, for example, a username must contain only some characters, let’s say [a-zA-Z0-9_-.]. The length varies between (x and n) where x and n (integers, x <=n). Rule: creating exact filters and validation rules are best practices for me.

  2. Use other tools: Here, I will also agree with you that a prepared statement (parametrized query) and stored procedures. The disadvantages here is these ways require advanced skills which do not exist for most users. The basic idea here is to distinguish between the SQL query and the data that is used inside. Both approaches can be used even with unsafe data, because the user-input data here does not add anything to the original query, such as (any or x=x).

For more information, please read OWASP SQL Injection Prevention Cheat Sheet.

Now, if you are an advanced user, start using this defense as you like, but, for beginners, if they can't quickly implement a stored procedure and prepared the statement, it's better to filter input data as much they can.

Finally, let's consider that a user sends this text below instead of entering his/her username:

[1] UNION SELECT IF(SUBSTRING(Password,1,1)='2',BENCHMARK(100000,SHA1(1)),0) User,Password FROM mysql.user WHERE User = 'root'

This input can be checked early without any prepared statement and stored procedures, but to be on the safe side, using them starts after user-data filtering and validation.

The last point is detecting unexpected behavior which requires more effort and complexity; it's not recommended for normal web applications.

Unexpected behavior in the above user input is SELECT, UNION, IF, SUBSTRING, BENCHMARK, SHA, and root. Once these words detected, you can avoid the input.

UPDATE 1:

A user commented that this post is useless, OK! Here is what OWASP.ORG provided:

Primary defenses:

Option #1: Use of Prepared Statements (Parameterized Queries)
Option #2: Use of Stored Procedures
Option #3: Escaping all User Supplied Input

Additional defenses:

Also Enforce: Least Privilege
Also Perform: White List Input Validation

As you may know, claiming an article should be supported by a valid argument, at least by one reference! Otherwise, it's considered as an attack and a bad claim!

Update 2:

From the PHP manual, PHP: Prepared Statements - Manual:

Escaping and SQL injection

Bound variables will be escaped automatically by the server. The server inserts their escaped values at the appropriate places into the statement template before execution. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. See the mysqli_stmt_bind_param() function for more information.

The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements if input values are escaped correctly.

Update 3:

I created test cases for knowing how PDO and MySQLi send the query to the MySQL server when using a prepared statement:

PDO:

$user = "''1''"; // Malicious keyword
$sql = 'SELECT * FROM awa_user WHERE userame =:username';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':username' => $user));

Query Log:

    189 Query SELECT * FROM awa_user WHERE userame ='\'\'1\'\''
    189 Quit

MySQLi:

$stmt = $mysqli->prepare("SELECT * FROM awa_user WHERE username =?")) {
$stmt->bind_param("s", $user);
$user = "''1''";
$stmt->execute();

Query Log:

    188 Prepare   SELECT * FROM awa_user WHERE username =?
    188 Execute   SELECT * FROM awa_user WHERE username ='\'\'1\'\''
    188 Quit

It's clear that a prepared statement is also escaping the data, nothing else.

As also mentioned in the above statement,

The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements, if input values are escaped correctly

Therefore, this proves that data validation such as intval() is a good idea for integer values before sending any query. In addition, preventing malicious user data before sending the query is a correct and valid approach.

Please see this question for more detail: PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result

References:

  1. SQL Injection Cheat Sheet
  2. SQL Injection
  3. Information security
  4. Security Principles
  5. Data validation

回答22:

安全警告:此答案与安全最佳做法不符。 转义不足以防止SQL注入,请使用准备好的语句。使用以下概述的策略需要您自担风险。 (此外,在PHP 7中删除了mysql_real_escape_string()。)

不建议使用的警告:目前不建议使用mysql扩展名。我们建议使用 PDO扩展名

我使用三种不同的方法来防止我的Web应用程序容易受到SQL注入的攻击。<​​/ p>

  1. 使用mysql_real_escape_string(),它是 PHP中的预定义函数,此代码将反斜杠添加到以下字符:\x00\n\r\ '"\x1a。将输入值作为参数传递,以最大程度地减少SQL注入的机会。
  2. 最高级的方法是使用PDO。

我希望这会对您有所帮助。

考虑以下查询:

$iId=mysql_real_escape_string("1OR1=1");$ sSql =" SELECT * FROM table WHERE id = $ iId";

mysql_real_escape_string()不会在这里保护。如果在查询中对变量使用单引号(''),则可以防止这种情况。这是下面的解决方案:

$iId=(int)mysql_real_escape_string("1OR1=1");$ sSql =" SELECT * FROM table WHERE id = $ iId";

问题关于以下内容有一些好的答案这个。

我建议使用PDO是最好的选择。

修改:

从PHP 5.5.0开始不推荐使用

mysql_real_escape_string()。使用mysqli或PDO。

mysql_real_escape_string()的替代方法是

string mysqli_real_escape_string ( mysqli $link , string $escapestr )

示例:

$iId = $mysqli->real_escape_string("1 OR 1=1");
$mysqli->query("SELECT * FROM table WHERE id = $iId");

A22:

Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also, mysql_real_escape_string() was removed in PHP 7.)

Deprecated Warning: The mysql extension is deprecated at this time. we recommend using the PDO extension

I use three different ways to prevent my web application from being vulnerable to SQL injection.

  1. Use of mysql_real_escape_string(), which is a pre-defined function in PHP, and this code add backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a. Pass the input values as parameters to minimize the chance of SQL injection.
  2. The most advanced way is to use PDOs.

I hope this will help you.

Consider the following query:

$iId = mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";

mysql_real_escape_string() will not protect here. If you use single quotes (' ') around your variables inside your query is what protects you against this. Here is an solution below for this:

$iId = (int) mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";

This question has some good answers about this.

I suggest, using PDO is the best option.

Edit:

mysql_real_escape_string() is deprecated as of PHP 5.5.0. Use either mysqli or PDO.

An alternative to mysql_real_escape_string() is

string mysqli_real_escape_string ( mysqli $link , string $escapestr )

Example:

$iId = $mysqli->real_escape_string("1 OR 1=1");
$mysqli->query("SELECT * FROM table WHERE id = $iId");

回答23:

一种简单的方法是使用 CodeIgniter Laravel 具有内置功能,例如过滤和活动记录,因此您不必担心这些细微差别。

A23:

A simple way would be to use a PHP framework like CodeIgniter or Laravel which have inbuilt features like filtering and active-record so that you don't have to worry about these nuances.

回答24:

警告:此答案中描述的方法仅适用于非常特定的方案,并且并不安全,因为SQL注入攻击不仅依赖于能够注入X=Y。 / strong>

如果攻击者试图通过PHP的$_GET变量或URL的查询字符串来入侵表单,则在它们不安全的情况下,您将能够将其捕获。

RewriteCond %{QUERY_STRING} ([0-9]+)=([0-9]+)
RewriteRule ^(.*) ^/track.php

因为1=12=21=22=1 1 + 1 = 2 等...是攻击者的SQL数据库的常见问题。也许它也被许多黑客应用程序所使用。

但是您必须小心,不要重写您网站上的安全查询。上面的代码为您提供了提示,可以将特定于黑客的动态查询字符串重写或重定向(取决于您)到一个页面中,该页面将存储攻击者的 IP地址,甚至是他们的Cookie,历史记录,浏览器或任何其他敏感信息,因此您以后可以通过禁止其帐户来处理它们或与当局联系。

A24:

Warning: the approach described in this answer only applies to very specific scenarios and isn't secure since SQL injection attacks do not only rely on being able to inject X=Y.

If the attackers are trying to hack into the form via PHP's $_GET variable or with the URL's query string, you would be able to catch them if they're not secure.

RewriteCond %{QUERY_STRING} ([0-9]+)=([0-9]+)
RewriteRule ^(.*) ^/track.php

Because 1=1, 2=2, 1=2, 2=1, 1+1=2, etc... are the common questions to an SQL database of an attacker. Maybe also it's used by many hacking applications.

But you must be careful, that you must not rewrite a safe query from your site. The code above is giving you a tip, to rewrite or redirect (it depends on you) that hacking-specific dynamic query string into a page that will store the attacker's IP address, or EVEN THEIR COOKIES, history, browser, or any other sensitive information, so you can deal with them later by banning their account or contacting authorities.

回答25:

PHP和MySQL 的答案很多,但这是 PHP和Oracle 的代码,用于防止SQL注入以及常规使用oci8驱动程序:

$conn = oci_connect($username, $password, $connection_string);
$stmt = oci_parse($conn, 'UPDATE table SET field = :xx WHERE ID = 123');
oci_bind_by_name($stmt, ':xx', $fieldval);
oci_execute($stmt);

A25:

There are so many answers for PHP and MySQL, but here is code for PHP and Oracle for preventing SQL injection as well as regular use of oci8 drivers:

$conn = oci_connect($username, $password, $connection_string);
$stmt = oci_parse($conn, 'UPDATE table SET field = :xx WHERE ID = 123');
oci_bind_by_name($stmt, ':xx', $fieldval);
oci_execute($stmt);

A26:

A good idea is to use an 'object-relational mapper' like Idiorm:

$user = ORM::for_table('user')
->where_equal('username', 'j4mie')
->find_one();

$user->first_name = 'Jamie';
$user->save();

$tweets = ORM::for_table('tweet')
    ->select('tweet.*')
    ->join('user', array(
        'user.id', '=', 'tweet.user_id'
    ))
    ->where_equal('user.username', 'j4mie')
    ->find_many();

foreach ($tweets as $tweet) {
    echo $tweet->text;
}

It not only saves you from SQL injections but from syntax errors too! Also Supports collections of models with method chaining to filter or apply actions to multiple results at once and multiple connections.

回答27:

不建议使用的警告::该答案的示例代码(如问题的示例代码)使用PHP的MySQL扩展名,该扩展名在PHP 5.5.0中已弃用,并在PHP 7.0中被完全删除.0。

安全警告:此答案与安全最佳做法不符。 转义不足以防止SQL注入,请改用准备好的语句。使用以下概述的策略需要您自担风险。 (此外,在PHP 7中删除了mysql_real_escape_string()。)

使用 PDO MYSQLi 是防止SQL注入的好方法,但是如果您真的想使用MySQL函数和查询,最好使用

mysql_real_escape_string

$unsafe_variable = mysql_real_escape_string($_POST['user_input']);

还有更多的功能可以防止这种情况的发生:比如识别-如果输入是字符串,数字,字符或数组,那么有很多内置函数可以检测到这一点。另外,最好使用这些功能来检查输入数据。

is_string

$unsafe_variable = (is_string($_POST['user_input']) ? $_POST['user_input'] : '');

is_numeric

$unsafe_variable = (is_numeric($_POST['user_input']) ? $_POST['user_input'] : '');

使用这些功能通过mysql_real_escape_string检查输入数据要好得多。

A27:

Deprecated Warning: This answer's sample code (like the question's sample code) uses PHP's MySQL extension, which was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0.

Security Warning: This answer is not in line with security best practices. Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also, mysql_real_escape_string() was removed in PHP 7.)

Using PDO and MYSQLi is a good practice to prevent SQL injections, but if you really want to work with MySQL functions and queries, it would be better to use

mysql_real_escape_string

$unsafe_variable = mysql_real_escape_string($_POST['user_input']);

There are more abilities to prevent this: like identify - if the input is a string, number, char or array, there are so many inbuilt functions to detect this. Also, it would be better to use these functions to check input data.

is_string

$unsafe_variable = (is_string($_POST['user_input']) ? $_POST['user_input'] : '');

is_numeric

$unsafe_variable = (is_numeric($_POST['user_input']) ? $_POST['user_input'] : '');

And it is so much better to use those functions to check input data with mysql_real_escape_string.

回答28:

几年前,我已经写了这个小功能:

function sqlvprintf($query, $args)
{
    global $DB_LINK;
    $ctr = 0;
    ensureConnection(); // Connect to database if not connected already.
    $values = array();
    foreach ($args as $value)
    {
        if (is_string($value))
        {
            $value = "'" . mysqli_real_escape_string($DB_LINK, $value) . "'";
        }
        else if (is_null($value))
        {
            $value = 'NULL';
        }
        else if (!is_int($value) && !is_float($value))
        {
            die('Only numeric, string, array and NULL arguments allowed in a query. Argument '.($ctr+1).' is not a basic type, it\'s type is '. gettype($value). '.');
        }
        $values[] = $value;
        $ctr++;
    }
    $query = preg_replace_callback(
        '/{(\\d+)}/', 
        function($match) use ($values)
        {
            if (isset($values[$match[1]]))
            {
                return $values[$match[1]];
            }
            else
            {
                return $match[0];
            }
        },
        $query
    );
    return $query;
}

function runEscapedQuery($preparedQuery /*, ...*/)
{
    $params = array_slice(func_get_args(), 1);
    $results = runQuery(sqlvprintf($preparedQuery, $params)); // Run query and fetch results.   
    return $results;
}

这允许在单行C#-ish String中运行语句。格式如下:

runEscapedQuery("INSERT INTO Whatever (id, foo, bar) VALUES ({0}, {1}, {2})", $numericVar, $stringVar1, $stringVar2);

考虑变量类型可以逃脱。如果尝试对表的列名进行参数化,则会失败,因为它将每个字符串都放在引号中,这是一种无效的语法。

安全性更新:先前的str_replace版本允许通过向用户数据中添加{#}令牌来进行注入。如果替换包含这些令牌,则此preg_replace_callback版本不会引起问题。

A28:

I've written this little function several years ago:

function sqlvprintf($query, $args)
{
    global $DB_LINK;
    $ctr = 0;
    ensureConnection(); // Connect to database if not connected already.
    $values = array();
    foreach ($args as $value)
    {
        if (is_string($value))
        {
            $value = "'" . mysqli_real_escape_string($DB_LINK, $value) . "'";
        }
        else if (is_null($value))
        {
            $value = 'NULL';
        }
        else if (!is_int($value) && !is_float($value))
        {
            die('Only numeric, string, array and NULL arguments allowed in a query. Argument '.($ctr+1).' is not a basic type, it\'s type is '. gettype($value). '.');
        }
        $values[] = $value;
        $ctr++;
    }
    $query = preg_replace_callback(
        '/{(\\d+)}/', 
        function($match) use ($values)
        {
            if (isset($values[$match[1]]))
            {
                return $values[$match[1]];
            }
            else
            {
                return $match[0];
            }
        },
        $query
    );
    return $query;
}

function runEscapedQuery($preparedQuery /*, ...*/)
{
    $params = array_slice(func_get_args(), 1);
    $results = runQuery(sqlvprintf($preparedQuery, $params)); // Run query and fetch results.   
    return $results;
}

This allows running statements in an one-liner C#-ish String.Format like:

runEscapedQuery("INSERT INTO Whatever (id, foo, bar) VALUES ({0}, {1}, {2})", $numericVar, $stringVar1, $stringVar2);

It escapes considering the variable type. If you try to parameterize table, column names, it would fail as it puts every string in quotes which is an invalid syntax.

SECURITY UPDATE: The previous str_replace version allowed injections by adding {#} tokens into user data. This preg_replace_callback version doesn't cause problems if the replacement contains these tokens.

回到顶部