Author: Haseeb Ahmad Basil

Posted on: 2 days ago

Categories:php Tutorials

Since PHP 7 was introduced and PHP 5 end of support life was reached, many hosting companies dropped PHP 5 from their servers and PHP developers were forced to migrate their code to work with PHP 7.

One of the changes of PHP 7 was the discontinuation of the original mysql extension. Developers had to choose to migrate their code to use either the MySQLi or and the PDO extension.

Read this article to learn about the differences and decide which is the best for you to migrate your code to work on PHP 7 or later, as well learn about some recommended packages that use PDO or MySQLi for performing common database purposes like backup, security, user registration and login, database abstraction, ORM (Object-Relational Mapping), etc..

ByHaseeb Ahmad Basil
MySQLi vs PDO vs MySQL Difference, Performance Benchmark Comparison, Security an ...

< email contact >

Contents 1. PDO vs MySQLi vs MySQL 2. Difference to Connect Using MySQLi and PDO 3. PDO vs MySQLi vs MySQL Performance Benchmark 4. PDO vs MysQLi Security using Prepared Statements 5. Comparison between PDO and MySQLi Terms of Usage 6. Converting MySQL Applications to Use PDO or MySQLi 7. Package Recommendations for use with PDO and MySQLi 8. What to Use in a New Project: PDO or MySQLi?
MySQLi vs PDO vs MySQL Difference, Performance Benchmark Comparison, Security an ...
1. PDO vs MySQLi vs MySQL

As we all know, MySQL is an Open Source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). MySQL is a central component of the LAMP Open Source Web application software stack (and other "AMP" stacks): Apache MySQL and PHP.

MySQL is an essential part of almost every Open Source PHP application. Good examples for PHP and MySQL based scripts are phpBB, osCommerce and Joomla.

PHP used to come with the original MySQL extension built-in which supports with older MySQL versions. However this extension was deprecated in favor of MySQLi (i for improved). At the same time PHP continued to evolve and the PDO (PHP Data Objects) extension was introduced to become a common interface for accessing many types of database.

MySQLi is another one of three ways to access a MySQL database server. Like the MySQL extension, the new MySQLi was designed to take better advantage of more recent MySQL server features.

The PHP Data Objects (PDO) extension defines a lightweight, common interface for accessing databases in PHP. Each database driver that is supported by PDO interface can expose database specific features, as well common functions.

PDO provides a data access abstraction layer, which means that, regardless of which database type you use, the same functions are available to perform queries and fetch results. PDO does not provide a full database abstraction. i.e. it does not rewrite SQL queries or emulate missing features. You should use a full-blown abstraction layer package if you need that capability.

2. Difference to Connect Using MySQLi and PDO

Either PDO and MySQLi offer an Object Oriented interface to the extension functions but MySQLi also offers a procedural API, which makes it easier for newcomers to understand. If you are familiar with the original PHP MySQL extension, you will find migration to the procedural MySQLi interface easier. Below is an example:

// PDO $pdo = new PDO( "mysql:" . "host=localhost;" . "dbname=database", 'username', 'password'); // mysqli, procedural way $mysqli = mysqli_connect( 'localhost', 'username', 'password', 'database'); // mysqli, object oriented way $mysqli = new mysqli( 'localhost', 'username', 'password', 'database');

The main advantage of PDO over MySQLi is in the database support. PDO supports 12 different database types, in opposition to MySQLi, which supports MySQL only.

When you have to switch your project to use another database, PDO makes the process simpler. So all you have to do is change the connection string and at most a few queries if they use any syntax which is not supported by your new database.

3. PDO vs MysQLi Security using Prepared Statements

Both PDO and MySQLi provide support for prepared queries. This helps preventing SQL injection security issues, as long as you only use prepared queries to insert dynamic parameters in the queries.

For example, consider a hacker that tries to inject malicious SQL passing a forged value to the parameter code of a HTTP POST request that could be emulated like this:

$_POST['code'] = "'; DELETE FROM products; /*";

If you do not escape this value, it will be inserted in query as is, and so it would delete all rows from the products table.

One way to make queries more secure avoiding SQL injection is to quote string parameter values to insert escape characters.

// PDO, manual escaping $name = PDO::quote( $_POST['code'] ); $pdo->query( "SELECT id, name FROM products WHERE code = $code" ); // mysqli, manual escaping $name = mysqli_real_escape_string( $_POST['code'] ); $mysqli->query( "SELECT id, name FROM products WHERE name = '$code'" );

PDO::quote() not only escapes the string, but it also adds quotes. mysqli_real_escape_string() will only escape the string, so you will need to add the quotes manually.

// PDO, prepared statement $pdo->prepare( 'SELECT id, name FROM products WHERE code = :code' ); $pdo->execute( array( ':code' => $_POST['code'] ) ); // mysqli, prepared statements $query = $mysqli->prepare('SELECT id, name FROM users WHERE code = ?'); $query->bind_param('s', $_POST['code']); $query->execute();

PDO also supports client side queries. This means that when it prepares a query, it does not have to communicate with the server.

Since MySQLi uses native prepared statements, it will may actually be faster to use mysqli_real_escape_string instead of using prepared statements, while it is still a secure solution.

4. PDO vs MySQLi vs MySQL Performance Benchmark

There were some PHP MySQL performance benchmark tests several years ago by Jonathan Robson as well by Radu Potop . Even though these tests were performed with PHP 5.3 and nowadays we are using PHP 7 or later, let's consider these results as reference.

Basically they show that for SELECT queries using prepared statements MySQLi runs a bit faster. Still it may not be significant depending on your purposes.

Keep in mind that PDO by default uses client side prepared statements emulation. When using native prepared statements, there is an additional round trip to the server to prepare the statement, so the overall query execution time may be actually greater than when using native prepared statements for running a query only once.

As mentioned above, you can use mysqli_real_escape_string function to quote dynami

本文开发(php)相关术语:php代码审计工具 php开发工程师 移动开发者大会 移动互联网开发 web开发工程师 软件开发流程 软件开发工程师

tags: PDO,MySQLi,MySQL,PHP,code,database,mysqli,vs
本文标题:MySQLi vs PDO vs MySQL Difference, Performance Benchmark Comparison, Security an ...

技术大类 技术大类 | 开发(php) | 评论(0) | 阅读(99)