Store '\abc\def\fg.jpg' Problem in MySQL

Question

I need to store relative path of a document into a MySQL table. The problem is that when I insert a string of this form:

$urlPath ='\abc\def\fg.jpg'

into the relevant column, what I get is, I have all the slash '\' strip off, with an unknown symbol in front of all the text. So the result in the MySQL table is something like this:

'(weird symbol)abcdeffg.jpg'

As you can see, this kind of data is useless. Any idea how to fix this?

Edit: I tried $urlPath ="\\abc\\def\\fg.jpg" and $urlPath ='\\abc\\def\\fg.jpg', still I got gibberish..

Answer 1

Try this...

$urlPath ='\\abc\\def\\fg.jpg'

Answer 2

Use mysql_real_escape_string( ) to escape special characters in your string before using this string in an insert/update query.

The backslashes are special characters in PHP and MySQL so they need to be escaped at the both levels -- PHP and MySQL.

Answer 3

the \ character is an escape character for many programming and scripting languages, including php.

You'll have to use double \ to escape it. The same goes if you want to enter symbols like ' " and so on into a database, or use " within a string, something like $mystr = "I can have \" in my string";

Answer 4

You need to escape it twice, first for PHP because the \ character has a special meaning:

From the docs:

To specify a literal backslash before a single quote, or at the end of the string, double it (\)

$urlPath ='\\abc\\def\\fg.jpg';

Then you need to escape it again for MySQL because the literal string now contains \'s which also has special meaning to MySQL.

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

$databaseUrlPath = mysql_real_escape_string($urlPath);

If you don't want to depend on MySQL, you can use addslashes instead:

Returns a string with backslashes before characters that need to be quoted in database queries etc. These characters are single quote ('), double quote ("), backslash () and NUL (the NULL byte).

$databaseUrlPath = addslashes($urlPath);

Answer 5

what I mean is, I want the code that works even without any MySQL connection opened.

Sorry, that's not possible: you do actually need a connection to do proper escaping, because escaping methods are connection-specific:

1. In some character sets, the 0x27 and 0x5C bytes (' and \) may be part of a multi-byte character, in which case backslash-escaping it will generate the wrong results and may actually introduce an SQL injection possibility! So you have to know the connection's character set to escape the right ' characters rather than bytes.

2. If the server is using the configuration option NO_BACKSLASH_ESCAPES to be ANSI SQL compliant, backslash-escaping is the wrong thing to do and won't protect against SQL injection.

So you do need mysql_real_escape_string and a connection object to correctly escape a string literal. This isn't usually a problem because the right time to mysql_real_escape_string something is just the moment before it goes into the executed query. You shouldn't be passing around mysql-escaped strings otherwise.

If you don't use/support the ANSI options, and either you're sure your database connection will always be using a single-byte character set or you're sure non-ASCII characters will never appear, you can safely use addslashes instead, which does not require a connection.

But better is to use parameterised queries so you don't have to think about any of this stuff.