Difference between Mysqli and PDO in PHP.
For accessing a database in PHP, we have got two choices: MySQLi (MySQL improved) and PDO (PHP Data Objects) . It is always good to know about these in detail before choosing one.
The major differences are listed here.
- Database Support
- Database Connection.
|FreeTDS / Microsoft SQL Server / Sybase|
|IBM Informix Dynamic Server|
|Oracle Call Interface|
|ODBC v3 (IBM DB2, unixODBC and win32 ODBC)|
|SQLite 3 and SQLite 2|
|Microsoft SQL Server / SQL Azure|
$pdo = new PDO("mysql:host=localhost.com;dbname=data", 'username', 'password');
Mysqli, procedural style
$mysqli = mysqli_connect('localhost.com','username','password','data');
Mysqli,object oriented style
$mysqli = new mysqli('localhost.in','username','password','data');
Both PDO and MySQLi provide SQL injection security, as long as developer follow them with features like escaping and parameter binding with prepared statements.
For example a hacker is trying to inject some malicious SQL through the ‘name’ HTTP query parameter (POST):
$_POST['name'] = "'; DELETE FROM users; /*"
If we fail to escape, it will be added in query “as it is” it will delete all rows from users table as PDO and MySQLi support multiple queries.
// PDO, “manual” escaping
$name = PDO::quote($_POST['name']);
$pdo->query("SELECT * FROM users WHERE name = $name");
// mysqli, “manual” escaping
$name = mysqli_real_escape_string($_POST['name']);
$mysqli->query("SELECT * FROM users WHERE name = '$name'");
PDO::quote() not only escapes the string, but it also quotes it. But mysqli_real_escape_string() will only escape the string, you will need to apply the quotes manually.
// PDO, prepared statement
$pdo->prepare('SELECT * FROM users WHERE name = :name');
$pdo->execute(array(':name' => $_POST['name']));
// mysqli, prepared statements
$query = $mysqli->prepare('SELECT * FROM users WHERE name = ?');
I always recommend you to use prepared statements with bound queries instead of PDO::quote() and mysqli_real_escape_string().
Both PDO and MySQLi are quite fast. For SELECT query, MySQLi was about 2.5% faster for non-prepared statements and about 6.5% faster for prepared statements
|Database support||12 different drivers||MySQL only|
|API||OOP||OOP + procedural|
Finally, MySqli is native for php and works very well with MySql. So, you can choose Mysqli only if you are working on Mysql database and not familiar with PDO statements. Its always recommended to prefer PDO, as it supports twelve different database drivers including Mysql database. Performance point of view Mysqli is better and security point of view, both are safe as long as the developer follows recommended statements.