What is the most secure way to deal with data in mysql database

Question

is using PDO more secure that the normal classic "mysql_query" ? or is there other more secure ways to import and export data from mysql db

I heard the using PDO could cause you problems, is it right?

Answer 1

I'd use PDO with parameters binding rather than simple mysql_query. This will ensure you won't have to deal with SQL injection issues with your project

Here's a simple example i took from php.net docs:

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>

using pdo this way is definitely more safe. It'll also handle variables casting. I'm using PDO (on the low level with additional abstraction level) in all my current php projects and i never had any problems with that.

Answer 2

In general importing / exporting data from database is not any more or less secure if you would use mysql_query rather than PDO wrapper. The important thing you need to take care of is to secure the insecure source of data. Generally - don't trust anything that comes from user via mechanisms such as GET, POST, COOKIE etc. and is used as parameters for your code - consider it all malicious data and first validate into format you expect.

PDO is "more secure" in a sense that it is easy to parametrize your SQL query and avoid SQL injection type of attacks which are usually the most common types of web attacks. For example:

<?php
$id = (isset(_$GET['id']))? _$GET['id'] : ''; // Possibly unsafe data from user
$dbh = new PDO('mysql:host='.$host.';dbname='.$name, $user, $pass);
$sth = $dbh->prepare("SELECT * FROM `table` WHERE `id`=?");
$sth->execute(array($id));
?>

By using parametrized queries like above where placeholder ? is replaced by specific data query expects there, you effectively prevent SQL injection from happening because PDO then considers data you got from _$GET['id'] as just a "data" instead of something that might be part of a query string (which may be used to cause SQL injection).

If for example you would directly insert GET data by appending it to query like:

$query = "SELECT * FROM `table` WHERE `id`=".$_GET['id'];

This could easily lead to SQL injection. Don't ever do that. That last part of SQL can be used in all sorts of ways to inject additional SQL you never intended to execute if the _$GET variable is not validated or filtered. If in above example user supplied the following string in the _$GET['id'] - "1;DROP TABLE table;" that would effectively drop your table with users - a thing you never intended to do as then a resulting query would look like:

"SELECT * FROM `table` WHERE `id`=1;DROP TABLE table;"

You should do everything you can to prevent getting invalid data in the first place for example if you expect integer you might do:

$id = (isset(_$GET['id']))? _$GET['id'] : ''; // Possibly unsafe data from user
$id = intval($id);  // Make sure the data is integer at all times
if ($id < 0) $id = 0; // Make sure it is positive integer at all times

That way you may ensure the data is in expected format.

By first validating the data, and then using parametrized query to insert into database you ensure in a way multi-layered security which helps you getting rid of invalid entries in your database as well as invalid code behaviour. As suggested by "Your Common Sense" in comments below it doesn't make it absolutely secure for all kinds of attacks but it does decrease their likelihood significantly.

intval is not the only function that can help you with that, you can also examine:

http://php.net/manual/en/function.filter-var.php

What kind of problems regarding PDO are you referring to?

Answer 3

There is nothing essentially secure in the function names you are using.

It's the way using them could be secure (or not). One can make mysql_query no less secure than PDO. And contrary - PDO can easily "could cause you problems" if used improperly.

So, the most secure way to deal with data in mysql query (not database) is:

1. every dynamical literal should be added to the query via placeholder only
2. if plcaceholder cannot be used - value have to be whitelisted against hardcoded list of allowed values

As PDO offers support for placeholders out of the box, it is preferred over mysql_query in general. However, this support is very limited, and if you want to have full protection, you have to create your own solution, or use some ready-made library like SafeMySql