How to prevent SQL injection in PHP?
P粉128563140
2023-08-31 18:10:38
<p>If user input is inserted into a SQL query without modification, the application is vulnerable to SQL injection, as in the following example: </p>
<pre class="lang-php prettyprint-override"><code>$unsafe_variable = $_POST['user_input'];
mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");
</code></pre>
<p>This is because the user can enter something like <code>value'); DROP TABLE table;--</code>, the query becomes: </p>
<pre class="brush:php;toolbar:false;">INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')</pre>
<p>What can be done to prevent this from happening? </p>
To use parameterized queries, you need to use Mysqli or PDO. To rewrite your example using mysqli we would need the following.
The key function you need to read is
mysqli::prepare
.Also, as others have suggested, you may find using something like PDO.
Please note that the case you are asking about is fairly simple, more complex cases may require more sophisticated methods. in particular:
mysql_real_escape_string
does not contain the required escapes. In this case, you'd be better off passing the user's input through a whitelist to ensure only "safe" values are allowed through.No matter which database you use, the correct way to avoid SQL injection attacks is to separate the data from the SQL so that the data is still data and > never Will be interpreted as a command by the SQL parser. It is possible to create SQL statements with properly formatted data portions, but if you don't understand the details at all, you should always use prepared statements and parameterized queries. is a SQL statement 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:PDO (for any supported database driver):
MySQLi (for MySQL):
Starting with PHP 8.2, we can use execute_query() to prepare, bind parameters and execute SQL statements in one method:
Up to PHP8.1:
pg_prepare()
and
pg_execute()for PostgreSQL) . PDO is a universal option.
Set up the connection correctly
PDO to access a MySQL database, real prepared statements are not used by default . To resolve this issue, you must disable simulation of prepared statements. An example of creating a connection using PDO is:
In the example above, error mode is not strictly necessary,but it is recommended to add it . This way, PDO will notify you of all MySQL errors by throwing PDOException
However,.
forcing is the first setAttribute()
While you can set theline, which tells PDO to disable simulated prepared statements and use
real prepared statement statements . This ensures that statements and values are not parsed by PHP before being sent to the MySQL server (giving a would-be attacker no chance to inject malicious SQL).character set
Mysqliin the options of the constructor, it is important to note that "older" versions of PHP (prior to 5.3.6)
silently ignore the Character set parameters .illustrate
prepare
What matters here is that the parameter value is combined with the compiled statement, not the SQL string. SQL injection works by tricking a script into containing a malicious string when it creates the SQL to be sent to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something unexpected.are parsed and compiled by the database server. You tell the database engine where you want to filter by specifying parameters (
?or named parameters, like
:namein the example above). Then, when you call
execute, the prepared statement is combined with the parameter values you specified.
Any parameters you send when using prepared statements will be treated as strings (although the database engine may do some optimizations, so parameters may of course end up being treated as numbers as well). In the example above, if the
$name
variable contains'Sarah'; DELETE FROMEmployees
the result is simply the search string"'Sarah'; DELETE FROMEmployees"
, and You will not end up with an empty table.Another benefit of using prepared statements is that if you execute the same statement multiple times in the same session, it will only be parsed and compiled once, thus improving speed.
Oh, since you asked how to do the insert, here's an example (using PDO):
Can prepared statements be used for dynamic queries?
While you can still use prepared statements with query parameters, the structure of the dynamic query itself cannot be parameterized, and certain query functions cannot be parameterized either.
For these specific scenarios, the best approach is to use a whitelist filter to limit the possible values.