Only select data containing backslashes in MySQL

Question

I am trying to fix database entries that have been double escaped. I believe that magic_quotes was turned on and mysql_real_escape_string was used. Double escaping resulted in incorrect / missing some search results.

I disabled magic_quotes and plan to search for backslash entries and update them to remove any double escaping. The problem is that when I execute a query to search for backslash entries, I always get no results.

SELECT title FROM exampletable WHERE title LIKE '%\\\\%' 

I am using '% \\\\%' as recommended here: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

If I print every heading, many of them have unwanted backslashes, so I know they are there. I just can't isolate them in the request.

 Example Data: Old King\ Road Running Down A Dream Can\'t Stop The Sun This One For Me 

Again, just trying to get the records back with \ in them.

---

EDIT: MySQL version 5.0.92-community. Collation - latin1_swedish_ci. Charset UTF-8 Unicode

% \\% Does not work. I tried. It is listed as invalid on mysql.com:

To find "\", specify it as "\\\\"; this is due to the fact that back braids break out once by the parser and again when the pattern matches, leaving a single backslash that needs to be matched.

Answer 1

This command works for me when I run the command with php:

 select * from exampletable where title like '%\\\\\\\\%' or title like '\\\\\\\\%' or title like '%\\\\'; 

The reason I include starting, lower, and ending matches is to show that they are different. If you are looking for a backslash at the end, there should only be 4 backslashes. this is because there is nothing to hide after him. You use 8 backslashes on other commands because they are handled by php.

Answer 2

Your query works fine for me in MySQL 5.5.8:

 mysql> create table exampletable ( title varchar(255) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.15 sec) mysql> mysql> insert into exampletable values ('Old King\\' Road'), ('Running Down A Dream'),('Can\\''t Stop The Sun'),('This One' For Me'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> mysql> select * from exampletable WHERE title LIKE '%\\\\%'; +---------------------+ | title | +---------------------+ | Old King\ Road | | Can\'t Stop The Sun | +---------------------+ 2 rows in set (0.01 sec) 

Answer 3

Try:

 SELECT title FROM exampletable WHERE title LIKE '%\\%' 

Answer 4

In the case of Ruby on Rails

 search_value.gsub!(/\\/, "\\\\\\\\\\") MyModel.where(["column_name ?","%#{search_value}%"]) 

If you do not want to use wild cards, add them.

 search_value.gsub!(/%/, "\\%").gsub!(/_/, "\\_")