HowTo Prevent SQL Injections with PDO

[Wizatek]

Hi,

 

I notice that there are many websites being used at this moment that are vulnerable of mysql injections.

U might think, what kind of harm could a sql injection really do.

Well trough a mysql injection people can influent your database, edit anything.

But that is far from all.

With a mysql injection a hacker can create a file on your server, preferably in your webroot.

This file can contain anything, and can be executed with the web browser.

Most are running their websites on their game servers, mostly logged in as Administrator.

That means this hacker can run that file, from their web browser, on your server, with Admin rights!

PHP is not limited to just doing web things, it can do a lot more, even execute other programs.

Maybe that program is also created with a mysql injection.

Maybe that program will give the hacker access to your firewall, or creates a user to login Remote Desktop.

Mysql injections are seriously dangerous!

 

 

Thats why im going to show u some techniques to stop that from happening completely

As a php scripter u probably use the mysql_ functions (mysql_connect, mysql_query, etc, etc)

These functions should no longer be used.

On the website of PHP u can find the same info

Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

 

MySQLi is not my personal favorite, so today i want to show PDO_MYSQL

I am not going to explain more about how mysql injections work, and how to use it and those things.

If u are searching for that information u can find tons of websites on google or videos on youtube.

 

 

Ok lets start at the beginning.

Connecting to the server.

 

Before we used something like this

mysql_connect('server','username','password');

 

in PDO we do this slightly different.

The reason for this is that u can use the same functions for connecting to several different database systems.

MySQL, MSSQL, Oracle, etc etc

 

$server = 'localhost';
$database = 'database';
$user = 'myusername';
$password = 'mypassword';

// First we have to create a DSN
$dsn = sprintf("mysql:host=%s;dbname=%s", $server, $database);

// Then we can use that DSN to connect to the database with PDO
// I use try/catch statements to not show some to revealing error messages when the connection fails
try
{
$db = new PDO($dsn, $user, $password);
}
catch(PDOException $e)
{
die('Error connecting to the database');
}

 

That was all it takes to connect to the database.

I know its a little more work then u are used to, but the easy way isnt always the best way.

 

 

Ok now we are going to query the database and get some results.

Lets assume we want to login our game account on the website.

There is a form on the website that sends the username and password.

 

First, the wrong way

$username = $_POST['username'];
$password = $_POST['password'];

$result = mysql_query("SELECT user_code FROM bg_user WHERE user_id = '$username' AND passwd = '$password'");
$row = mysql_fetch_row($result);

 

Now the correct way

$username = $_POST['username'];
$password = $_POST['password'];

// Prepare the query with placeholders
$dbh = $db->prepare("SELECT user_code FROM bg_user WHERE user_id = :username AND passwd = :password");

// Execute the query
$dbh->execute( array( ':username' => $username,
':password' => $password ));

// Get the result
$row = $dbh->fetch();

 

Ok lets break this apart a little because it might look just as unsafe, but really, it isnt.

 

$dbh = $db->prepare("SELECT user_code FROM bg_user WHERE user_id = :username AND passwd = :password");

This prepares the query, it sends the query to the Mysql Connector with the placeholders :username and :password

The mysql connector then checks what kind of data should come in place of the :username or :password

is it a varchar, int, bigint or whatever it needs to be.

Then it will add the quotes itself, when needed.

At this point the query is prepared, and ready to be executed.

 

$dbh->execute( array( ':username' => $username,
':password' => $password ));

This is where we tell the mysql connector which values the placeholders should be replaced with.

The mysql connector then checks if the variables are from the right type, it needs a number, is it a number?

When it needs a number, and its not a number then the query will not be executed.

When it needs a string it will automatically add the slashes before every ' and " so escaping those

is not needed anymore.

 

$row = $dbh->fetch();

This will only get the results in a array for 1 row only, exactley the same like mysql_fetch_row();

 

 

Lets take the last example and change it a little to show all accounts that start with a W

 

$username = 'W%';

// Prepare the query with placeholders
$dbh = $db->prepare("SELECT user_id FROM bg_user WHERE user_id LIKE :username");

// Execute the query
$dbh->execute( array( ':username' => $username ));

// Get the result
$rows = $dbh->fetchAll();

// Show the results
foreach( $rows as $row )
{
echo $row['user_id'] . "<br />";
}

 

The reason why i use a LIKE statement here is because i want to show how to use it with prepared statements.

U can not use :username% directly in the query.

 

I want to end this little tutorial with some easy conversions

 

mysql_num_rows -> $dbh->rowCount()
mysql_error() -> $db->errorInfo()
mysql_error() -> $dbh->errorInfo()
mysql_fetch_row -> $dbh->fetch()
mysql_fetch_array -> $dbh->fetchAll()

 

This was just a really really small tutorial, but it should give enough info to get your website a lot more secure.

More info about this can be found 2 and 2

But i will be happy to answer questions about it in this thread also.