Today we will discuss about the inequality between Mysql, Mysqli and PDO. Mysql/ mysqli is the common functions used in PHP for database connection. Out of these function mysql is totally depreciated but mysqli still exists. These two functions are used in older version of PHP.
Mysqli is basically the upgrade of mysql and it has replaced the use of mysql in websites now.
So guys what is the difference between the following three :-
- PDO
- Mysql
- Mysqli
Syntax for Database Connection using Mysql, Mysqli and PDO
<?php
//PDO Connection
$pdo = newPDO("mysql:host=localhost.com;dbname=data", 'username', 'password');
// Mysqli Connection
$mysqli= mysqli_connect('localhost.com','username','password','data');
//Mysql Connection
$mysqli = new mysqli('localhost.com','username','password','data');
?>
Mysql is less secure as compare to Mysqli and PDO because we cannot use prepare statements in it.
Mysqli supports only Mysql driver but PDO supports 12 database drivers.
//For getting PDO Driver Details run below command
var_dump(PDO::getAvailableDrivers());
Mysql does not have sql injection security but PDO and Mysqli have.
However synatax of all connection method is different. Mysqli is almost similar to Mysql and you can easily upgrade your code to Mysqli but PDO is totally different from both. Syntax of PDO queries is almost similar to Mysqli but way to run query is different.
For example in PDO and Mysqli we can name parameters to get , update or delete.
<?php
//Mysqli
$devildoxx = $mysqli->prepare("SELECT `name` FROM `users` WHERE `id` = ?");
$devildoxx->bind_param('i', $id);
$devildoxx->execute();
//PDO
$devildoxx= $pdo->prepare("SELECT * FROM USERS WHERE id=:id ");
$devildoxx ->execute(array(':id'=> $id));
$devildoxx->fetchAll(PDO::FETCH_ASSOC);
?>
You can see that binding method is similar in both but way to execute and bind is different. PDO and Mysqli both are very secure so thats why mysql is depreciated in all versions.
What are SQL Injections ?
Before starting this topic you must know what is sql injection ?
SQL Injection is basically database attack that hackers try to damage the database. Hackers try to inject these malicious commands in login form etc to attack the website. PDO and Mysqli both has SQL Injection security inbuilt.
For example if you are using Mysql then Hackers can attack you by posting commands like
<?php$_POST['name'] ="'; SELECT * FROM TABLENAME; /*";?>
If someone posted similar to above in PDO Query then syntax will be ..
<?php
$name = PDO::quote($_POST['name']);
$pdo->query("SELECT * FROM TABLENAME WHERE name = '$name'");
?>
PDO will prevent the hacker to attack by adding quotes in string to avoid sql injection so if hacker post any malicious code then it will not work. Same thing is in Mysqli.
<?php
$name= mysqli_real_escape_string($_POST['name']);
$mysqli->query("SELECT * FROM TABLENAME name = '$name'");
?>
If you are using prepared statements of PDO and Mysqli then both are secured and can avoid any type of SQL Injection attack.
