How to use PDO to query Mysql in Php to avoid the risk of SQL injection

When we use the traditional mysql_connect and mysql_query methods to connect and query the database, if the filtering is not strict, there is a risk of SQL injection, causing the website to be attacked and out of control. Although the mysql_real_escape_string() function can be used to filter user-submitted values, it also has flaws. By using the prepare method of PHP's PDO extension, you can avoid the risk of sql injection.

PDO (PHP Data Object) is a major new feature added to PHP5, because before PHP 5, php4/php3 had a bunch of database extensions to connect and process various databases, such as php_mysql.dll. PHP6 will also use PDO to connect by default, and the mysql extension will be used as an auxiliary. Official: http://php.net/manual/en/book.pdo.php

1. PDO configuration
Before using the PDO extension, you must first enable this extension. In PHP.ini, remove the ";" in front of "extension=php_pdo.dll" to connect Database, you also need to remove the ";" sign in front of the database extension related to PDO (usually php_pdo_mysql.dll is used), and then restart the Apache server.

Copy code The code is as follows:

extension=php_pdo.dll
extension=php_pdo_mysql.dll

2. PDO connects to mysql database

Copy code The code is as follows:

$dbh = new PDO("mysql:host=localhost;dbname=db_demo","root","password");

The default is not a persistent connection. If you want to use a database persistent connection, you need to add it at the end The following parameters:

Copy code The code is as follows:

$dbh = new PDO("mysql:host=localhost;dbname= db_demo","root","password","array(PDO::ATTR_PERSISTENT => true)");
$dbh = null; //(release)

3. PDO setting properties

1) PDO has three error handling methods:

• PDO::ERrmODE_SILENT does not display error messages, only sets error codes
• PDO::ERrmODE_WARNING displays warning errors
• PDO::ERrmODE_EXCEPTION throws exceptions

You can use the following statement to set the error handling method to throw an exception

Copy the code The code is as follows:

$db ->setAttribute(PDO::ATTR_ERrmODE, PDO::ERrmODE_EXCEPTION);

When set to PDO::ERrmODE_SILENT, you can get the error information by calling errorCode() or errorInfo(), of course others It's also possible.

2) Because different databases handle the case of returned field names differently, PDO provides the PDO::ATTR_CASE setting item (including PDO::CASE_LOWER, PDO::CASE_NATURAL, PDO::CASE_UPPER) to determine the returned The case of field names.

3) Specify the corresponding value in php for the NULL value returned by the database by setting the PDO::ATTR_ORACLE_NULLS type (including PDO::NULL_NATURAL, PDO::NULL_EmpTY_STRING, PDO::NULL_TO_STRING).

4. Common PDO methods and their applications
PDO::query() is mainly used for operations that return recorded results, especially SELECT operations
PDO::exec() Mainly for operations that do not return a result set, such as INSERT, UPDATE and other operations
PDO::prepare() is mainly a preprocessing operation, and you need to use $rs->execute() to execute the SQL statement in the preprocessing. This method can bind parameters and is quite powerful (preventing SQL injection depends on this)
PDO::lastInsertId() returns the last insertion operation. The primary key column type is the last auto-incremented ID
PDOStatement: :fetch() is used to get a record
PDOStatement::fetchAll() is used to get all records into a collection
PDOStatement::fetchColumn() is used to get a certain field of the first record specified in the result. If it is missing The province is the first field
PDOStatement::rowCount(): mainly used for the result set affected by PDO::query() and PDO::prepare()'s DELETE, INSERT, and UPDATE operations, and for PDO::exec () method and SELECT operation are invalid.

5. PDO operation MYSQL database instance

Copy code The code is as follows:

< ?php
$pdo = new PDO("mysql:host=localhost;dbname=db_demo","root","");
if($pdo -> exec("insert into db_demo(name, content) values('title','content')")){
echo "Insertion successful! ";
echo $pdo -> lastinsertid();
}
?>

Copy code The code is as follows:

$pdo = new PDO("mysql:host=localhost;dbname=db_demo","root","");
$rs = $pdo -> query("select * from test");
$rs->setFetchMode(PDO::FETCH_ASSOC); //Associative array form
//$rs->setFetchMode(PDO::FETCH_NUM); / /numeric index array form
while($row = $rs -> fetch()){
print_r($row);
}
?>

Copy code The code is as follows:

foreach( $db->query( "SELECT * FROM feeds" ) as $row )
{
 print_r( $row );
}
?>

Count how many rows of data there are

Copy code The code is as follows:

$sql="select count(*) from test";
$num = $dbh-> query($sql)->fetchColumn();

prepare method

Copy code The code is as follows:

$stmt = $dbh->prepare("select * from test");
if ($stmt->execute()) {
 while ($row = $stmt-> ;fetch()) {
 print_r($row);
 }
}

Prepare parameterized query

Copy code The code is as follows:

$stmt = $dbh->prepare("select * from test where name = ?");
if ($stmt-> execute(array("david"))) {
 while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
 print_r($row);
 }
} 

[Let’s talk about the key points, how to prevent sql injection]

When using PDO to access the MySQL database, real prepared statements are not used by default. To solve this problem, you must disable the emulation effects of prepared statements. The following is an example of using PDO to create a link:

Copy the code The code is as follows:

$dbh = new PDO('mysql: dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

setAttribute() line is mandatory and tells PDO to disable emulation of prepared statements and use real parepared statements. This ensures that the SQL statement and corresponding values are not parsed by PHP before being passed to the mysql server (disabling all possible malicious SQL injection attacks). Although you can set the character set attribute (charset=utf8) in the configuration file, it is important to note that older versions of PHP (< 5.3.6) ignore character parameters in DSN.

Let’s look at a complete code usage example:

Copy the code The code is as follows:

$dbh = new PDO ("mysql:host=localhost; dbname=demo", "user", "pass");
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); //Disable the simulation effect of prepared statements
$dbh->exec("set names 'utf8'");
$sql="select * from test where name = ? and password = ?";
$stmt = $dbh->prepare ($sql);
$exeres = $stmt->execute(array($testname, $pass));
if ($exeres) {
while ($row = $stmt-> fetch(PDO::FETCH_ASSOC)) {
print_r($row);
}
}
$dbh = null;

The above code can prevent this sql injection. Why?
When prepare() is called, the query statement has been sent to the database server. At this time, only the placeholder? is sent, and there is no user-submitted data; when execute() is called, the value submitted by the user will be Sent to the database, they are sent separately. The two are independent, and SQL attackers have no chance.

But we need to pay attention to the following situations. PDO cannot help you prevent SQL injection

1. You cannot let the placeholder ? replace a set of values, such as:

Copy the code The code is as follows:

SELECT * FROM blog WHERE userid IN (?);

2. You cannot let placeholders replace the data table name or column name, such as:

Copy code The code is as follows:

SELECT * FROM blog ORDER BY ?;

3. You cannot let the placeholder ? replace any other SQL syntax, such as:

Copy code The code is as follows:

SELECT EXTRACT( ? FROM datetime_column) AS variable_datetime_element FROM blog;