jonathanasdf

Serious Dabbler

Preventing SQL Injection with PDOs

leave a comment »

Yesterday I set up WAMP and was able to send a simple SELECT * query to the db, and retrieve static data I manually inserted in the db, through the built in mysql_connect and mysql_query functions. However, since I want to be able to handle a login system, the data being retrieved will be dynamic with the username and password specified by the user. As I noted before I have experience in SQL injection, and of course since now I’m the one working on the system I want to make sure that my setup prevents SQL injection as much as possible.

At first I thought about just restricting the characterset in the flash app, to stop people from typing invalid usernames in. But that’s only going to stop the copy-paste script kiddies, since it’s trivial to intercept the HTTP request and change the parameters directly. Then I thought about just hashing the username and password before using it. But that won’t work since I need to store email addresses in the database, and there’s not much use to storing a hashed email address. Of course I could encode it in some way such that I can decode it again later, but that’s really just avoiding the problem, and eventually in the future I’m going to run into a case where I can’t avoid the problem like this, so might as well learn how to solve it at this point.

And then I found out about the PHP filter functions from w3schools. But from various blogs and StackOverflow threads I saw that the filter functions are not completely reliable and that in general you do NOT want to filter inputs. I don’t particularly understand the arguments behind this so I would appreciate if someone would enlighten me about why the filter functions are not as preferrable a method of preventing SQL injections.

Anyways, from one of the posts in StackOverflow I ended up jumping to the PDO documentation. PDO stands for PHP Data Objects and implements what are called “Prepared Statements” – you first specify what is like a statement template, and specify the variables within that template. That template is parsed, and then you can set the variables within the template to various values and run the template with those variable values. Since the template is parsed by itself, and then input is just added afterwards, there is 0 chance for SQL injection.

The syntax itself is simple and easy to understand, and very flexible. From the nice site about preventing SQL injection at bobby-tables.com, modified a bit to show how to retrieve data from a SELECT, and including error handling (assuming pass has already been hashed. In my setup I hash the password with SHA1 before sending it to the php script):

// Set up the PDO with a connection to the server
try {
    $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
} catch (PDOException $e) {
    die("Error connecting to db");
}

// Create a prepared statement
$stmt = $dbh->prepare('SELECT * FROM users WHERE id = :id AND pass = :pass');

// Execute it with variables set to some value
if (!$stmt->execute( array('id' => $id, 'pass' => $pass) )) {
    die("Error executing the statement. Possibly syntax error.");
}

// Grab the resulting row from the SELECT statement. As id is unique there is at most 1 row.
// Otherwise, you would use a foreach to loop through the fetch().
if (!$row = $stmt->fetch()) {
    die("No records found with the given id and pass.");
}

Next step for me I guess is creating a website that handles registration, and creating the graphics for the login in the flash file.

Advertisements

Written by jonathanasdf

February 26, 2011 at 1:20 AM

Posted in Tutorials, WAMP

Tagged with , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s