The core method to prevent SQL injection is to use preprocessing statements and parameterized queries. 1. Use PDO preprocessing statements to bind user input through question marks or named parameters to ensure that the input is not executed as SQL code; 2. Use mysqli's preprocessing and bind_param method to clearly specify the parameter type to prevent malicious input from tampering with SQL structure; 3. Avoid manually escape input, such as mysqli_real_escape_string, because it is prone to errors and insufficient security; 4. Use PHP built-in filter functions to verify input, such as filter_input and intval, to ensure the legality of the input data. These methods can effectively improve the security of PHP applications and prevent SQL injection attacks.

Preventing SQL Injection is a very basic but critical security measure in PHP development. Directly splicing SQL statements is very dangerous. Attackers can tamper with SQL logic by constructing malicious input, or even delete or steal data. PHP provides a variety of ways to prevent SQL injections, and here are some practical methods.

Use Prepared Statements

This is the most recommended way. Preprocessing statements ensure that user input is not executed as SQL code by processing SQL statements and parameters separately.

Take PDO (PHP Data Objects) as an example:

 $pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = ? AND password = ?');
$stmt->execute([$username, $password]);
$user = $stmt->fetch();

You can also use named parameters to make the code clearer:

 $stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
$stmt->execute(['username' => $username, 'password' => $password]);

This method can effectively prevent SQL injections because the parameters are later bound and will not affect the original SQL structure.

Use mysqli's binding parameter function

If you are using the mysqli extension, you can also use its preprocessing function:

 $mysqli = new mysqli("localhost", "user", "password", "test");
$stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);
$stmt->execute();
$stmt->close();

Here "ss" means two string parameters. This method can also effectively prevent SQL injections.

Not recommended: Manual escape input

Although mysqli_real_escape_string() can escape input strings, this method is not safe and error-prone, and is not recommended:

 $username = mysqli_real_escape_string($mysqli, $_POST['username']);
$query = "SELECT * FROM users WHERE username = '$username'";

This writing method looks simple, but it is easy to leave loopholes due to splicing logic errors. For example, if you forget to add quotes when splicing numeric fields, the attacker can inject it.

Additional suggestions: Verify and filter input

In addition to using preprocessing statements, you can also verify user input with PHP filtering functions, for example:

 $email = filter_input(INPUT_POST, 'email', FILTER_VALIDATE_EMAIL);

This allows filtering out illegal input before executing SQL, further improving security.

In addition, for numeric inputs, you can use intval() or FILTER_VALIDATE_INT to ensure that the input is an integer:

 $id = intval($_GET['id']);

Basically that's it. The core of preventing SQL injections is to not treat user input as part of SQL, but to use parameterized queries. Although the method seems simple, it is easily overlooked in actual development. As long as you develop the habit of using preprocessing statements, the risk of SQL injection can be greatly reduced.

The above is the detailed content of php function to prevent sql injection. For more information, please follow other related articles on the PHP Chinese website!