如果用户输入未经修改就插入到 SQL 查询中，那么应用程序很容易受到SQL 注入的攻击，就像在 下面的例子：
$unsafe_variable = $_POST['user_input'];
mysql_query(“INSERT INTOtable(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;--')
可以做些什么来防止这种情况发生？
The correct way to avoid SQL injection attacks, no matter which database you use, is to separate the data from SQL, so that data stays data and will never be interpreted as commands by the SQL parser. It is possible to create SQL statement with correctly formatted data parts, but if you don't fully understand the details, you should always 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:
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
}
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.
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.
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 ]);
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';
}
Deprecated Warning: This answer's sample code (like the question's sample code) uses PHP's
MySQLextension, 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:
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.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.Every answer here covers only part of the problem. In fact, there are four different query parts which we can add to SQL dynamically: -
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.
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).
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
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.
I think if someone wants to use PHP and MySQL or some other dataBase server:
(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.
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 . “')”);
</div>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.
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'));
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
</div>