Problem
Today, I wanted to strip slashes from a comment field using SQL (INSTEAD of using php stripslashes).
Why you ask?
I was working on an application which had not considered stripslashes for formatting output.
So I had a look and realised. Do I change 40,000 lines of code to add Stripslashes, or 1 line at the SQL select statement?
OK, slight exaggeration, but you get what I mean.
Solution
MySQL
[sourcecode language=’sql’]
Select
Replace(field_name, ‘\’, ”) as stripped_field
from
table;
[/sourcecode]
Oracle
[sourcecode language=’sql’]
Select
Replace(field_name, ”, ”) as stripped_field
from
table;
[/sourcecode]
PHP EXTRA
If using in PHP add an extra backslash like this;
[sourcecode language=’sql’]
// strip slashes as part of the SQL select statement
$sql = “Select Replace(field_name, ‘\’, ”) as stripped_field from table”;
[/sourcecode]
Please get in touch if you know of an easier way to do this, for example, Oracle Functions, or MySQL functions.
Many thanks and happy coding.
James
Whoever coded the application in the first place coded it incorrectly. Your fix will remedy the problem but really you should do a…
UPDATE table SET field_name = replace(field_name, ”, ”);
This is assuming your data doesn’t have any backslashes in that you want to retain, otherwise you need to replace ‘s with ‘s, ‘t with ‘t etc.
Run that to get rid of slashes in the DB. You shouldn’t store data in the table with slashes in;
http://stackoverflow.com/questions/277575/shouldnt-mysql-real-escape-string-leave-slashes-in-database
But as you say, mammoth task to update the queries within the 40,000 lines of code.