Anup Saund

🇬🇧British Expat 🇨🇦Vancouver ❤ Gardening 💰Founder of gitSQL

Stripslashes in SQL for MYSQL and ORACLE

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

Select
Replace(field_name, '\', '') as stripped_field
from
table;

Oracle

Select
Replace(field_name, '', '') as stripped_field
from
table;

PHP EXTRA

If using in PHP add an extra backslash like this;

// strip slashes as part of the SQL select statement
$sql = "Select Replace(field_name, '\', '') as stripped_field from table";

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.

Previous

I remember the days before ADODB

Next

Website Design Trends 2010 / 2011?

1 Comment

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Powered by WordPress & Theme by Anders Norén