Welcome to this tutorial on using databases in PHP. I will be adding more sections in near future. If you have any questions/comments please contact me
The problem(s) with the mysql_* library (this post)
Using PDO - connecting to your db
Using PDO - Exceptions
Using PDO - Your first query
Using PDO - Parameterized queries
Using PDO - Fetch methods
Using PDO - When parameters just don’t do the trick
The problem(s) with the mysql_* library
If you are learning PHP, you have probably come across tutorials telling you to use mysql_* functions (mysql_connect, mysql_query, mysql_real_escape_string, etc), you are not part of the problem. The problem is the people writing these tutorials, spreading deprecated information teaching newcomers bad habits.
Let’s look at one of these tutorials, as an example
$con = mysql_connect(“localhost”, “username”, “password”);
die('Could not connect: ’ . mysql_error());
// some code to get username and password
// escape username and password for use in SQL
$username = mysql_real_escape_string($_POST[‘username’]);
$password = mysql_real_escape_string($_POST[‘password’]);
$sql = “SELECT * FROM users WHERE
user=’” . $username . “’ AND password=’” . $password . “’”
// more code
At least we are escaping the variables before inserting them into the query. One might argue that setting the variables username and password is wasting memory, but real world implications is probably close to zero.
So what is wrong with this code? It works!
Well, first of all mysql_real_escape_string does not protect you from all SQL injection attacks. I have had companies literally in shock over me handing them their databases from SQL injections because “it is not possible, we are escaping everything”. In order to properly avoid SQL injections when using the mysql_* library you have to properly escape everything, which you eventually will fail at.
In addition it results in messy code, as you will have escape functions everywhere.
ps: please do not end files with ?>, you should not stop PHP if you aren’t following it with HTML/output!
From PHP 5.5 the mysql_* functions will throw warnings, and they will later be removed from PHP alltogether.
Note: this tutorial requires PHP >= 5.5
Password hash / verify requires PHP >= 5.5
Fix with using another method of using passwords.
Short array referencing requires PHP >= 5.4
Fix with changing short arrays to old style arrays.
[php]$result = [‘test’];
[php]$result = array(‘test’);
Namespaces requires PHP >= 5.2
Fix with changing class names to not use namespaces
[php]$pdoConn = new \PDO…[/php]to:
[php]$pdoConn = new PDO…[/php]