Summary of PHP+mysql Methods for Preventing SQL Injection

In this paper, the method of preventing SQL injection by PHP+mysql is described with examples. Share it for your reference, as follows:

SQL injection

Example: Script logic

$sql = "SELECT * FROM user WHERE userid = $_GET[userid] ";

Case 1:

SELECT * FROM t WHERE a LIKE '%xxx%' OR (IF(NOW=SYSDATE(), SLEEP(5), 1)) OR b LIKE '1=1 ';

Case 2:

SELECT * FROM t WHERE a > 0 AND b IN(497 AND (SELECT * FROM (SELECT(SLEEP(20)))a) );

Case 3:

SELECT * FROM t WHERE a=1 and b in (1234 ,(SELECT (CASE WHEN (5=5) THEN SLEEP(5) ELSE 5*(SELECT 5 FROM INFORMATION_SCHEMA.CHARACTER_SETS) END)) );

Monitor the following methods

SLEEP ()-1-like SQL blind will appear with SLEEP () function, and 1-like SLEEP for at least 5 seconds
MID()
CHAR()
ORD()
SYSDATE()
SUBSTRING()
DATABASES()
SCHEMA()
USER()
VERSION()
CURRENT_USER()
LOAD_FILE()
OUTFILE/DUMPFILE
INFORMATION_SCHEMA
TABLE_NAME
fwrite () /fopen () /file_get_contents ()-These are the PHP file manipulation functions

Countermeasures:

1. mysql_escape_string() Escape Special Characters (PHP 4 > = 4.3. 0, PHP 5)) (mysql_real_escape_string must be linked to the database before an error is reported)

The following characters are affected:

\ x00//NULL corresponding to ascii character
\ n//Newline character and back to the front of the next 1 line
\ r//Line break
\//Escape character
'
"
\ x1a//Hexadecimal number

If successful, the function returns the escaped string. If it fails, false is returned.

2. addslashes() The function returns a string with a backslash preceded by a predefined character ( stripslashes() Implement string restoration)

The predefined characters are:

Single quotation mark (')
Double quotation marks (")
Backslash (\)
NULL

3. prepared statements (pretreatment mechanism)

<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
 echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
/* Non-prepared statement */
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
 echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
 echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Prepared statement, stage 2: bind and execute */
$id = 1;
if (!$stmt->bind_param("i", $id)) {
 echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
 echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
?>

More readers interested in PHP can check out the topics on this site: "php Programming Security Tutorial", "php Security Filtering Skills Summary", "PHP Operation and Operator Usage Summary", "PHP Basic Syntax Introduction Tutorial", "php Object-Oriented Programming Introduction Tutorial", "php String (string) Usage Summary", "php+mysql Database Operation Introduction Tutorial" and "php Common Database Operation Skills Summary"

I hope this article is helpful to everyone's PHP programming.