Do I need to use addslashes() when backing up database?

Question

I am using the code shown here, it uses addslashes() on the data fetched from the database before saving to file.

$row[$j] = addslashes($row[$j]);

My question is why and do I need to use this? I thought you would do this when saving to the database not the other way round. When I compare the results from the above script with the export from phpMyAdmin, the fields that contain serialized data are different. I would like to know if it would cause any problems when importing back into the database?

Script:

'a:2:{i:0;s:5:\"Hello\";i:1;s:5:\"World\";}'

phpMyAdmin Export:

'a:2:{i:0;s:5:"Hello";i:1;s:5:"World";}'

UPDATE

All data is escaped when inserting into the database.

Change from mysql to mysqli.

SQL file outputs like:

INSERT INTO test (foo, bar) VALUES (1, '\'single quotes\'\r\n\"double quotes\"\r\n\\back slashes\\\r\n/forward slashes/\r\n');

SOLUTION

Used $mysqli->real_escape_string() and not addslashes()

Answer 1

inserting to db

When inserting data to a MySQL database you should be either using prepared statements or the proper escape function like mysql_real_escape_string. addslashes has nothing to do with databases and should not be used. Escaping is used as a general term but actually covers a large number of operations. Here it seems two uses of escaping are being talked about:

1. Escaping dangerous values that could be inserted in to a database
2. Escaping string quotes to avoid broken strings

Most database escaping functions do a lot more than just escape quotes. They escape illegal characters and well as invisible characters like \0 ... this is because depending on the database you are using there are lots of ways of breaking an insert - not just by adding a closing quote.

Because someone seems to have missed my comment about mentioning PDO I will mention it again here. It is far better to use PDO or some other database abstraction system along with prepared statments, this is because you no longer have to worry about escaping your values.

outputting / dumping db values

In the mentioned backup your database script the original coder is using addslashes as a quick shorthand to make sure the outputted strings in the mysql dump are correctly formatted and wont break on re-insert. It has nothing to do with security.

selecting values from a db

Even if you escape your values on insert to the database, you will need to escape the quotes again when writing that data back in to any kind of export file that utilises strings. This is only because you wish to protect your strings so that they are properly formatted.

When inserting escaped data into a database, the 'escape sequences' used will be converted back to their original values. for example:

INSERT INTO table SET field = "my \"escaped\" value"

Once in the database the value will actually be:

my "escaped" value

So when you pull it back out of the database you will receive:

my "escaped" value

So when you need to place this in a formatted string/dump, a dump that will be read back in by a parser, you will need to do some kind of escaping to format it correctly:

$value_from_database = 'my "escaped" value';

echo '"' . $value_from_database . '"';

Will produce:

"my "escaped" value"

Which will break any normal string parser, so you need to do something like:

$value_from_database = 'my "escaped" value';

echo '"' . addslashes($value_from_database) . '"';

To produce:

"my \"escaped\" value"

However, if it were me I'd just target the double quote and escape:

$value_from_database = 'my "escaped" value';

echo '"' . str_replace('"', '\\"', $value_from_database) . '"';

Answer 2

I think you are mixing two problems. The first problem is SQL Injection and to prevent this you would have to escape the data going into the database. However by now there is a far more better way to do this. Using prepared statements and bound parameters. Example with PDO:

// setup a connection with the database
$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');    
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// run query
$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = :name');    
$stmt->execute(array(':name' => $name));

// get data
foreach ($stmt as $row) {
    // do something with $row
}

The other thing you would have to worry about it XSS attacks which basically allows a possible attacker to inject code into your website. To prevent this you should always use htmlspecialchars() when displaying data with possible information you cannot trust:

echo htmlspecialchars($dataFromUnsafeSource, ENT_QUOTES, 'UTF-8');

All data is escaped when inserting into the database.

When using prepared statements and bound paramters this isn't needed anymore.

Should I use addslashes() then use str_replace() to change \" to "?

addslashes() sounds like a crappy way to prevent anything. So not needed AFAICT.

Another note about accessing the database and in the case you are still using the old mysql_* function:

They are no longer maintained and the community has begun the deprecation process. See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide, this article will help to choose. If you care to learn, here is a good PDO tutorial.

Answer 3

You should store data without modifying them.

You should perform the needed escaping when outputting the data or putting them "inside" other data, like inside a database query.

Answer 4

just use mysql_escape_string() instead of addslashes and ereg_replace as written in david walsh's blog.

just try it it'll be better. :)