December 18th, 2012, 09:10 PM
How to handle slashes in mysql
I have an mysql insert problem that I need some help with. I was trying to insert a record that contained a string that was a file path. I made a mistake when I typed the value into a textbox: "\test_email_archive/xxxx.com/box/inbox/2012-09/"
As you can see I had the slash backwards. When the insert was done the record in mysql was valued:
est_email_archive/emarotta.com/box/inbox/2012-09/, with the "\" and "t" truncated. I assume this occurred because the slash was handled as an escape character.
I tried using addslashes and stripslashes, but I can't seem to get the syntax correct. The question I have is how to handle a situation where a user enters string into a text box with a "\" as part of the string so the value gets correctly entered into mysql (as entered)?
The query I have as it stands is:
$query = 'Insert into Email_StaffPath (StaffID, StaffPath) values(' . $StaffID . ',"' . $StaffPath . '")';
I also tried it reversing the single and double quotes.
$query = "Insert into Email_StaffPath (StaffID, StaffPath) values(" . $StaffID . ",'" . $StaffPath . "')";
December 18th, 2012, 09:38 PM
\t was actually taken a tab character, not just truncated.
Have a look at the contents of the field with
This should show you the invisible tab character lurking there.
select field_name, hex(field_name) from my_table;
To make sure any slashes are correctly handled, you should pass any string through mysql_real_escape_string()
This will fix any slashes, and stray apostrophes as well.
$query = "Insert into Email_StaffPath (StaffID, StaffPath) values(" . $StaffID . ",'" . mysql_real_escape_string($StaffPath) . "')";
December 19th, 2012, 12:46 AM
in addition to what BarryG already said:
this little slash problem points to massive security holes in your query code. When certain characters break the query, there's obviously something wrong. What if people actively use this to mess with your database (aka SQL injection)?
The problem is that you don't separate the actual query from the values you want to pass. It's all just one big string. So if users input a "wrong" value, it could easily interfer with the SQL (which is what just happened). To solve this, use prepared statements, which are available through the MySQLi extension or the PDO interface.
If you're still using the old "mysql_" functions, throw them away. They are long obsolete and will die out sooner or later. If you cannot possibly do that, because you've already written 100,000 lines of code, well, then use mysql_real_escape_string() as suggested by BarryG. But this is really just a workaround for old code. In any other case, use PDO or MySQLi.
Comments on this post
December 19th, 2012, 04:33 AM
There's a link in my sig for migrating from mysql_* functions to PDO, it's worth a look
Comments on this post