PDO (PHP Data Object), mysqli, and solutions to problems such as SQL injection

This is the sequel to the last http://www.cnblogs.com/charlesblc/p/5987951.html.

Some articles mention that mysqli and PDO support multiple queries, so the following URLs cause table data to be deleted.

http://localhost:8080/test.php?id=3;delete%20from%20users

But I am in the MySQL version of the function, above the SQL can not be executed. Does it not support multiple queries?

This article http://www.runoob.com/php/php-mysql-connect.html a few introductions to the mysqli, PDO way, not detailed.

This article mainly uses: http://blog.csdn.net/yipiankongbai/article/details/17277477

Three ways to connect:

// PDO   New PDO ("Mysql:host=localhost;dbname=database", ' username ', ' password ');      // mysqli, procedural   $mysqli = Mysqli_connect (' localhost ', ' username ', ' password ', ' database ');      // mysqli, object oriented   new mysqli (' localhost ', ' username ', ' password ', ' database ');  

First look at the mysqli process type:

<?Phpheader (' Content-type:text/html; Charset=utf-8 '); Echo"PHP version:". Php_version. "<br/>"; $con= Mysqli_connect (' 10.117.146.21:8306 ', ' root ', ' [Password] '); mysqli_select_db ($con,' Springdemo '); $input _id= Trim ($_get[' id ')]); $sql= ' Select nickname from user where id = '. $input _id;print_r (' SQL is: '. $sql. ' <br/> '); $result=mysqli_query ($con, $sql);if($result! =NULL) {Print_r (' Rows: '. Mysqli_num_rows ($result). ' <br/> ');  while($row =Mysqli_fetch_array ($result)) {Print_r ($row [' Nickname ']. ' <br/> '); }}mysqli_close ($con);?>

Test:

http://localhost:8080/test.php?id=3php Version:5.5.30= 3rows:1  Microhttp://localhost:8080/test.php?id=3%20or%201=1php Version:5.5.30= 3 or 1=1rows:4abcmicrohelloworld Hello

Add the Real_escape function:

?Phpheader (' Content-type:text/html; Charset=utf-8 '); Echo"PHP version:". Php_version. "<br/>"; $con= Mysqli_connect (' 10.117.146.21:8306 ', ' root ', ' [Password] '); mysqli_select_db ($con,' Springdemo '); $input _id= Mysqli_real_escape_string ($con, $_get[' id ']); $sql= ' Select nickname from user where id = '. $input _id;print_r (' SQL is: '. $sql. ' <br/> '); $result=mysqli_query ($con, $sql);if($result! =NULL) {Print_r (' Rows: '. Mysqli_num_rows ($result). ' <br/> ');  while($row =Mysqli_fetch_array ($result)) {Print_r ($row [' Nickname ']. ' <br/> '); }}mysqli_close ($con);?>

Test:

http://localhost:8080/test.php?id=3%20or%201=1php Version:5.5.30= 3 or 1=1  Rows:4abcmicrohelloworld Hello Note: There is still a problem because there is no quotation mark inside the URL! 

Use the recommended mysqli PreparedStatement method:

?Phpheader (' Content-type:text/html; Charset=utf-8 '); Echo"PHP version:". Php_version. "<br/>"; $con=NewMysqli (' 10.117.146.21:8306 ', ' root ', ' [Password] ', ' Springdemo ');//mysqli_select_db ($con, ' Springdemo ');$query= $con->prepare (' SELECT nickname from user WHERE id =? '); $query ->bind_param (' s ', $_get[' id ' )]); $query-execute (); $result = $query->get_result ();if($result! =NULL) {Print_r (' Rows: '. Mysqli_num_rows ($result). ' <br/> ');  while($row =Mysqli_fetch_array ($result)) {Print_r ($row [' Nickname ']. ' <br/> '); }}mysqli_close ($con);?>

Test:

http://localhost:8080/test.php?id=3php Version:5.5.30rows:1microhttp:  //localhost:8080/test.php?id=3%20or%201=1php Version:5.5.30rows:1  Micro

Comparison of PDO with mysqli:

performance PDO and mysqli all have very good performance. Under the benchmark test for non-prepared statements, the mysqli is slightly faster by 2. 5%, and prepared statements is 6.5%, can be said for performance is irrelevant.

	Pdo	Mysqli
Database Support	Different drivers	MySQL only
Api	Oop	OOP + Procedural
Connection	Easy	Easy
Named parameters	Yes	No
Object Mapping	Yes	Yes
Prepared statements (Client side)	Yes	No
Performance	Fast	Fast
Stored procedures	Yes	Yes

PDO mode:

<?Phpheader (' Content-type:text/html; Charset=utf-8 '); Echo"PHP version:". Php_version. "<br/>"; $pdo=NewPDO ("Mysql:host=10.117.146.21:8306;dbname=springdemo", ' Root ', ' [Password] ');className { Public$nickname;  Publicfunction info () {return' # '. $ This-nickname; }} $input _id= $_get[' id ']; $sql= ' Select nickname from user where id = '. $input _id;print_r (' SQL is: '. $sql. ' <br/> '); $result= $pdoquery ($sql); $result->setfetchmode (Pdo::fetch_class, ' Name ');if($result! =NULL) {   while($row = $resultFetch ()) {Print_r ($row->info (). ' <br/> '); }} $pdo=NULL;?>

Test:

http://localhost:8080/test.php?id=3php Version:5.5.30= 3#microhttp: // localhost:8080/test.php?id=3%20or%201=1 PHP version:5.5.30= 3 or 1=1#abc #micro#helloworld# Hello

Plus transcoding:

$input _id = $pdo->quote ($_get[' id ");

Test:

http://localhost:8080/test.php?id=3php Version:5.5.30= ' 3 '#microhttp:  //localhost:8080/test.php?id=3%20or%201=1php version:5.5.30sql is:select nickname from User where id = ' 3 or 1=1 ' #micro

Note that the quote of PDO automatically adds quotation marks to solve this problem.

http://localhost:8080/test.php?id=3%27%20or%201=%271php version:5.5.30sql is:select Nickname from user where id = ' 3\ ' or 1=\ ' 1 ' #micro

And it's no use trying to quote yourself. The quotation marks were transcoded, so they succeeded in preventing the attack.

PDO's prepared statement:

<? phpheader (' content-type:text/html; Charset=utf-8 '"PHP version:".) Php_version. "<br/>"new PDO ("Mysql:host=10.117.146.21:8306;dbname=springdemo", ' Root ', ' [Password] ' ); $prepared = $pdo->prepare (' Select nickname from user where id =: id '); $prepared->execute (Array (': id ' = = $_ge t[' id ')), while ($results = $prepared,Fetch (PDO::FETCH_ASSOC)) {    print_r ($results [' Nickname ']. ' <br/> ');} $pdo=null;? >

Experiment:

http://localhost:8080/test.php?id=3php Version:5.5.30microhttp:// localhost:8080/test.php?id=3%20or%201=1 PHP version:5.5.30Micro

The threat of SQL injection is no longer present.

There are several uses of a fetch in PDO:

value	Description
Pdo::fetch_assoc	Associative array form.
Pdo::fetch_num	A numeric index array form.
Pdo::fetch_both	Both arrays have the form, which is the default.
Pdo::fetch_obj	In the form of an object, similar to the previous mysql_fetch_object () function.
Pdo::fetch_bound	Returns the result as a Boolean value and assigns the obtained column value to the variable specified in the Bindparam () method.
Pdo::fetch_lazy	Returns the result as an associative array, a numeric index array, and an object in 3 form.

Change the process of the above procedure to prepared statement parameters:

<? phpheader (' content-type:text/html; Charset=utf-8 '"PHP version:".) Php_version. "<br/>"new PDO ("Mysql:host=10.117.146.21:8306;dbname=springdemo", ' Root ', ' [Password] ' );   = $pdo->prepare (' Select nickname from user where id =: id '); $prepared->bindparam (': Id ', $_get[' ID ']); $prepared->execute ();  while ($results =fetch (PDO::FETCH_ASSOC) $prepared) {    print_r ($results [' nickname ']. ' <br/> ');} $pdo=null;? >

After the experiment, the result will get the correct result for the above URLs.

Performance aspects:

Both PDO and mysqli have very good performance. In the non-prepared statements benchmark, the mysqli is slightly faster by 2.5%, while prepared statements is 6.5%, which can be said to be irrelevant for performance.
If you really care about this little bit of performance, and your own MySQL extension is faster than both, you can consider it.

The above section is from this article: http://blog.csdn.net/yipiankongbai/article/details/17277477 "PDO vs. Mysqli Choose which one?" 》

PDO (PHP Data Object), mysqli, and solutions to problems such as SQL injection