Introduction
If you are like me and most other people, you probably have not taken the time to learn about web security when you first started writing server-side code. This is very dangerous as most people never even go back and try to make their code secure (or they simply forget). Writing their code in the same way that they originally learned how to can cause some serious vulnerabilities in the code, allowing hacking techniques such as SQL Injections to be fairly easy. If you have no idea what MySQL injections or cross side scripting is, then you should do some research, for example just go to Google and type in "SQL Injections" and there will be plenty of reading for you. I also would recommend a book called, "How to Break Web Software", it is a fantastic book that one of my professors told one of my classes about. It can teach you a lot about security, it is highly recommended. I will have an article written shortly on SQL Injections, so check back soon! If you do know what some of these nasty hacking techniques are then you are probably wondering why you should want to use prepared statements. There are basically three reasons why you should seriously consider writing prepared statements to execute your queries.
1. Prepared statements are more secure.
2. Prepared statements have better performance.
3. Prepared statements are more convenient to write.
Now that we know why prepared statements are better, let’s build an example so you can see for yourself. We’ll build a simple login example using prepared statements. First, I’ll show you the way most people would write it, then I’ll show you the way you could do it with a prepared statement which will be more secure, have better performance and be more convenient to write. Let’s get started!
The Well-known Way
If you are reading this article, chances are you already know how to execute a simple MySQL query in PHP. For those of you who do not know how to do this, it would look similar to this:
/* Connect to the Database */
$dbLink = mysql_connect("localhost", "username", "password");if (!dbLink) {
echo 'db link fail';
}
/* Select the database */
mysql_select_db("databaseName");
/* Query and get the results */
$query = "SELECT * FROM testUsers WHERE username='$user' AND
password='$pass'";
$result = mysql_query($query);
/* Loop through the results */
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "Username: " . $row["username";
}
Prepared Statements
What is so great about prepared statements and why are they more secure? The simple answer is because prepared statements can help increase security by separating the SQL logic from the data being supplied. In the previous example we saw how the data is basically built into the SQL logic by building the query as a string on the fly. Let’s take a look at what a prepared statement can look like.
/* Create a new mysqli object with database connection parameters */
$mysqli = new mysql('localhost', 'username', 'password', 'db');if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
/* Create a prepared statement */
if($stmt = $mysqli -> prepare("SELECT priv FROM testUsers WHERE username=?AND password=?")) {
/* Bind parameters
s - string, b - boolean, i - int, etc */
$stmt -> bind_param("ss", $user, $pass);/* Execute it */
$stmt -> execute();
/* Bind results */
$stmt -> bind_results($result);/* Fetch the value */
$stmt -> fetch();echo $user . "'s level of priviledges is " . $result;
/* Close statement */
$stmt -> close();}
/* Close connection */
$mysqli -> close();Let’s take a look at where the security happens in these few lines:
if($stmt = $mysqli -> prepare("SELECT priv FROM testUsers WHERE username=?
AND password=?")) {
$stmt -> bind_param("ss", $user, $pass);
Instead of grabbing and building the query string using things like $_GET['username'], we have ?'s instead. These ?'s separate the SQL logic from the data. The ?'s are place holders until the next line where we bind our parameters to be the username and password. The rest of the code is pretty much just calling methods which you can read about by following some of the links at the end of the article. AND password=?")) {
$stmt -> bind_param("ss", $user, $pass);
I hope this was helpful to you and if you have any questions feel free to post some comments below!
Most Popular Articles Links
Hack your Own Web Project ? SQL Injection
The Stock Market Story
Hi there,
Please fix your code.
$mysqli -> close(); should be rewritten to
$mysqli->close();
PHP is not Erlang so there is not white spaces around scope resolution operator (->)
pcdinh AT phpvietnam
You didn't sell me at all. Your prepared statement code is longer, more complicated and not any safer. The only safety precaution is not inserting _GET code directly into the query.
If I filter and validate the data prior to using in query then your original code is shorter, less complicated, and JUST as safe.
Thnx!
Ok now Im confused. Im currently trying to work out security for my scripting before I continue with anymore coding as im very concious about it.
Ive been told by a few people that this is alot more secure but it still confuses me although this is the only tutorial ive come across that explains it so thanks.
However ... I am not a php guru and I still code the old way using php 4 methods.
Could you possibly provide some more examples for us with an explantion more easily to understand?
Great work as always :)