Learn how to use PDO in PHP in this simple tutorial - Preventing SQL Injection attacks
20 February 2014 By Bhavyanshu Parasher
Why PDO is required?
First thing first. Let us understand the importance of PDO and why we must not use
mysql_*functions are getting old. For a long time now
mysql_*has been at odds with other common SQL database programming interfaces. It doesn’t support modern SQL database concepts such as prepared statements, stored procs, transactions etc… and it’s method for escaping parameters with mysql_real_escape_string and concatenating into SQL strings is error prone and old fashioned. The other issue with
mysql_*is that it has had a lack of attention lately from developers, it is not being maintained… which could mean things like security vulnerabilities are not getting fixed, or it may stop working altogether with newer versions of MySQL. One more reason to use the all new PDO is that the mysql_ functions are all deprecated. PDO also has different drivers for different SQL database vendors which will allow you to easily use databases by other vendors without having to relearn a different interface. (though you will have to learn slightly different SQL probably). Instead of concatenating escaped strings into SQL, in PDO you bind parameters which is an easier and cleaner way of securing queries. Binding parameters also allow for a performance increase when calling the same SQL query many times with slightly different parameters. PDO also has multiple methods of error handling. The biggest issue I have seen with
mysql_*code is that it lacks consistent handling. With PDO in exception mode, you can get consistent error handling which will end up saving you loads of time tracking down issues.
How to PDO?
Now that we know why we should learn and use PDO, let us begin with some coding.
Creating a PDO object
Putting PDO in Exception Mode
Basically the setAttribute() method helps in enabling various debug and error handling modes for PDO. To understand the full list of errors and error handling, read this manual page.
Executing a Query using PDO
We have learned how to connect to the MySQL DB using PDO. Now we must understand how to use prepared statements, exception handling and query the MySQL database. Just follow the comments in the code below.
NOTE: fetch() & fetchALL() with FETCH_ASSOC tells PDO to return the rows as an associative array with the field names as keys. Other fetch modes like PDO::FETCH_NUM returns the row as a numerical array. The default is to fetch with PDO::FETCH_BOTH which duplicates the data with both numerical and associative keys. It’s recommended you specify one or the other so you don’t have arrays that are double the size! PDO can also fetch objects with PDO::FETCH_OBJ, and can take existing classes with PDO::FETCH_CLASS. It can also bind into specific variables with PDO::FETCH_BOUND and using bindColumn method.
Using Prepared Statements and binding variables to query
That’s all for PDO. I hope you start applying it in all your PHP web applications to prevent them from getting hacked by SQL Injection attacks.
blog comments powered by Disqus