How to prevent SQL injection attacks in PHP

In web applications, SQL injection attacks are a common attack method. It takes advantage of the application's failure to filter or restrict user input and insert malicious SQL statements into the application, causing the database to be controlled by the attacker and steal sensitive data. For PHP developers, how to effectively prevent SQL injection attacks is a skill that must be mastered.

This article will introduce the best practices for preventing SQL injection attacks in PHP. It is recommended that PHP developers follow the following steps to protect their applications.

1. Use prepared statements

Prepared statements are a best practice to prevent SQL injection attacks in PHP. It defines the parameter placeholders of the SQL statement before sending the SQL query statement to the database. The parameters in the query are then bound with placeholders and executed against the database, thus avoiding maliciously injected SQL statements.

The following is an example of using PDO prepared statements to execute SQL queries:

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
$stmt->execute([$username]);
$results = $stmt->fetchAll();

In this example, $pdo is a PDO connection object, $ username is the username to be queried. The prepare() method defines a query preprocessing statement and uses placeholders ? instead of parameters. execute()The method binds the parameters in the prepared statement to the placeholders and executes the query to the database. Finally, store the query results in the $results variable.

2. Use parameterized queries

Parameterized queries are another best practice to prevent SQL injection attacks in PHP. Similar to prepared statements, it also uses placeholders to replace required query parameters, but it explicitly defines placeholders in the SQL query statement.

The following is an example of executing a SQL query using mysqli parameterized queries:

$stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param('s', $username);
$stmt->execute();
$results = $stmt->get_result();

In this example, $mysqli is a mysqli connection object, $ username is the username to be queried. The prepare() method defines a parameterized statement of a query and uses placeholders ? instead of parameters. bind_param()The method binds the placeholder to $username. Finally, call the execute() method to execute the query, and the get_result() method to obtain the query results.

Using the parameterized query method requires one more step of binding parameters compared to the prepared statement method, which is relatively troublesome to use. However, parameterized queries are more flexible and can better handle some complex SQL statements.

3. Use filters

PHP has a large number of built-in filter functions that can be used to filter and verify input values. Using appropriate filter functions, you can ensure that input values ​​conform to a specific format or specification and prevent input values ​​from being used for SQL injection attacks.

The following is an example of filtering user input using the filter_input() function:

$username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);
$password = filter_input(INPUT_POST, 'password', FILTER_SANITIZE_STRING);

In this example, the filter_input() function is used Filter the username and password entered by the user. The first parameter INPUT_POST specifies the filtered input type, which here refers to the POST request. The second parameters username and password are the variable names passed in the POST request respectively. The third parameter FILTER_SANITIZE_STRING is used to filter and remove all illegal characters and retain the letters and numbers in the string.

Using filters is equivalent to a layer of verification of user input on the client side. Different filter functions can filter different types of input, which can help developers effectively prevent SQL injection attacks.

4. Restrict database user permissions

Finally, ensure that database users have only minimal permissions to access the database. Database users who only have modification, insertion, deletion and other operation permissions but no query and selection permissions will not be able to execute queries containing illegal instructions, thereby preventing malicious SQL injection attacks.

In short, preventing SQL injection attacks in PHP is crucial. By using prepared statements, parameterized queries, filters, and restricting database user permissions, developers can protect their applications from malicious attacks.

The above is the detailed content of How to prevent SQL injection attacks in PHP. For more information, please follow other related articles on the PHP Chinese website!