Shulou(Shulou.com)06/01 Report--

This article mainly explains "Mysql how to avoid SQL injection risk". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian to study and learn "Mysql how to avoid SQL injection risk" together.

When we use the traditional mysql_connect, mysql_query method to connect to the query database, if the filtering is not strict, there is a risk of SQL injection, resulting in the website being attacked and losing control. While it is possible to filter user-submitted values with mysql_real_escape_string(), there are drawbacks. Using the prepare method of PHP PDO extension, you can avoid sql injection risks.

PDO(PHP Data Object) is a major addition to PHP 5, because prior to PHP 5 php4/php3 was a bunch of database extensions to connect and process with various databases, such as php_mysql.dll. PDO will also be used by default in PHP6, and the mysql extension will be used as an adjunct. Official: php.net/manual/en/book.pdo.php

1. PDO configuration

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

extension=php_pdo.dll

extension=php_pdo_mysql.dll

PDO connects to mysql database

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

The default is not a long connection. To use a database long connection, you need to add the following parameters at the end:

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

$dbh = null; //(release)

3. PDO Settings 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 error

PDO::ERrmODE_EXCEPTION throws an exception

You can set error handling to throw an exception by

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

Error messages can be obtained by calling errorCode() or errorInfo() when PDO::ERrmODE_SILENT is set, but other cases are possible.

2)Because different databases treat the returned field names differently, PDO provides PDO::ATTR_CASE settings (PDO::CASE_LOWER, PDO::CASE_NATURAL, PDO::CASE_UPPER) to determine the case of the returned field names.

3)By setting PDO::ATTR_ORACLE_NULLS type (including PDO::NULL_NATURAL, PDO::NULL_EmpTY_STRING, PDO::NULL_TO_STRING) to specify the NULL value returned by the database in php.

4. PDO common methods and their applications

PDO::query() is mainly used for operations with record results, especially SELECT operations.

PDO::exec() is mainly for operations that do not return a result set, such as INSERT, UPDATE, etc.

PDO::prepare() is mainly a preprocessing operation. You need to execute the SQL statement in the preprocessing through $rs->execute(). This method can bind parameters and is more powerful (preventing SQL injection depends on this).

PDO::lastInsertId() Returns the last insertion operation, the primary key column type is autoincrement, the last autoincrement ID

PDOStatement::fetch() is used to fetch a record

PDOStatement::fetchAll() is to get all recordsets into a collection

PDOStatement::fetchColumn() is a field that gets the first record specified in the result, the default is the first field

PDOStatement::rowCount() : is primarily a result set affected by Delete, INSERT, UPDATE operations for PDO::query() and PDO::prepare(), and has no effect on PDO::exec() methods and SELECT operations.

5. PDO operation MYSQL database instance

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.