MySQL server version 10.3.9-MariaDB can not query text has single quote

Question

I have a MySQL database that has a filed TagName with value ~!@#$%^&*()_+|}{":?><./';[]\=-` I try the query select TagName from taginfo where TagName like '%@#$%';

It show data with tagname contain quote character. But I query with = operator and like operator and add more quote '' to accept single quote but it show empty result.

I also try to add COLLATE UTF8_GENERAL_CI or alter CHARACTER SET but all are not success.

SELECT * from taginfo where tagname like '%~!@#$%^&*()_+|}{":?><./'';[]\=-`%';
SELECT * from taginfo where tagname like '%~!@#$%^&*()_+|}{":?><./'';[]\=-`%' COLLATE UTF8_GENERAL_CI;
SELECT * from taginfo where tagname COLLATE UTF8_GENERAL_CI like '%~!@#$%^&*()_+|}{":?><./'';[]\=-`%';

ALTER TABLE mytable CONVERT TO CHARACTER SET UTF8_GENERAL_CI
Error   2/19/2019 10:03:24 AM   0:00:00.039 <link> - MySQL Database Error: Unknown character set: 'UTF8_GENERAL_CI' 5   0

DB server version: MySQL 5.5.5 MariaDB Table information:

Here is result query without single quote:

Updated: I found a problem that if i query without character \ it show result:

select TagName from taginfo where TagName like '%~!@#$%^&*()_+|}{":?><./'';[]%';

But I added a character \ at the end it does not show anything:

select TagName from taginfo where TagName like '%~!@#$%^&*()_+|}{":?><./'';[]\%';

add more splash still not work

select TagName from taginfo where TagName like '%~!@#$%^&*()_+|}{":?><./'';[]\\%';

Updated: The problem now, the like query return result but the = query not return any.

SELECT * from taginfo where tagname like '%~!@#$%^&*()_+|}{":?><./'';[]\\=-`%';
select * from taginfo where TagName =     '~!@#$%^&*()_+|}{":?><./'';[]\\=-`'

Updated: When I try to created DB in MySQL 8.0.13, this query work well and return 1 row

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';

But in 10.3.9-MariaDB, the query

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';

can not return any result.

SELECT VERSION();

10.3.9-MariaDB

what about escaping two single quotes in the middle before `;` — danblack, Feb 19 '19 at 01:28
because the text has only quote, i add more quote to mysql accept it — Hien Nguyen, Feb 19 '19 at 01:32
Please show us the actual data you are trying to match. It is not clear exactly what you are trying to do here. — Tim Biegeleisen, Feb 19 '19 at 01:32
@danblack Two literal single quotes is an escaped single quote, and should not cause the query to break. — Tim Biegeleisen, Feb 19 '19 at 01:32
Possible duplicate of [How to search for slash (\‌) in MySQL? and why escaping (\‌) not required for where (=) but for Like is required?](https://stackoverflow.com/questions/14926386/how-to-search-for-slash-in-mysql-and-why-escaping-not-required-for-wher) — Nick, Feb 19 '19 at 01:51
Your query ```SELECT * from taginfo where tagname like '%~!@#$%^&*()_+|}{":?><./'';[]\\=-`%';``` is incorrect. You have to escape % and _ otherwise these characters will 'match' something you may be not expecting. — fifonik, Feb 19 '19 at 02:17
I don't think any change to the `COLLATION` will change the results for _this_ string. `COLLATION` mostly applies to comparisons of letters, especially accented letters. — Rick James, Feb 19 '19 at 04:29

score 1 · Accepted Answer · answered Mar 12 '19 at 06:34

Finally I found problem because I set this mode.

SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES');

and this query return empty

select * from taginfo where tagname = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`'

When I remove the NO_BACKSLASH_ESCAPES by SET @@SQL_MODE = 'NO_ENGINE_SUBSTITUTION';

select * from taginfo where tagname = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`'

it returns row with tagname = ~!@#$%^&*()_+|}{":?><./';[]\=-`

GMB · Answer 2 · 2019-02-19T07:22:49.420

0

The backslash is an escaping character in MySQL. You would need to double escape it, like \\. Also, if using a LIKE condition, the percent sign and the underscore would need to be escaped too (otherwise you migt match on irrelevant values).

You can see what is happening by simply selecting the value that you are passing :

SELECT '%~!@#$\%^&*()_+|}{":?><./'';[]\=-`%' test1;

| test1                            |
| -------------------------------- |
| %~!@#$%^&*()_+|}{":?><./';[]=-`% |

The single backslash just disappears. MySQL thinks that it is there to escape something, but since the following character (=) is not actually a special character, nothing more happens.

Now let's double escape the backslah, and it does appear in the output :

SELECT '%~!@#$\%^&*()\_+|}{":?><./'';[]\\=-`%' test2;

| test2                             |
| --------------------------------- |
| %~!@#$%^&*()_+|}{":?><./';[]\=-`% |

View on DB Fiddle

edited Feb 19 '19 at 07:22

answered Feb 19 '19 at 01:35

GMB

216,147
25
84
135

I try your query select * from taginfo where tagname = '%~!@#$%^&*()_+|}{":?><./'';[]\\\=-`%' but it does not work – Hien Nguyen Feb 19 '19 at 01:39
The same logic says that underscore (`_`) needs to be escaped. – Rick James Feb 19 '19 at 04:28
Can you help me with equal opearator @GMB? – Hien Nguyen Feb 19 '19 at 05:34

Hien Nguyen · Answer 3 · 2019-02-19T01:48:33.580

0

I try to set

SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES');

And update query add double \

SELECT * from taginfo where tagname like '%~!@#$%^&*()_+|}{":?><./'';[]\\=-`%';

This query return right result but not work with = operator.

edited Feb 19 '19 at 01:48

answered Feb 19 '19 at 01:37

Hien Nguyen

24,551
7
52
62

fifonik · Answer 4 · 2019-02-19T01:56:55.163

0

DROP TABLE IF EXISTS `taginfo`;

CREATE TABLE `taginfo` (
    `tagname` VARCHAR(50) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

-- Notice single quote and backslash are escaped:
INSERT INTO `taginfo` (`tagname`) VALUES   ('~!@#$%^&*()_+|}{":?><./'';[]\\=-`'); 
SELECT * FROM `taginfo` WHERE tagname =     '~!@#$%^&*()_+|}{":?><./'';[]\\=-`'; 

-- Notice single quote, percent and underscore are escaped and backslash escaped twice
SELECT * FROM `taginfo` WHERE tagname LIKE  '~!@#$\%^&*()\_+|}{":?><./'';[]\\\\=-`';
SELECT * FROM `taginfo` WHERE tagname LIKE '%~!@#$\%^&*()\_+|}{":?><./'';[]\\\\=-`%';

edited Feb 19 '19 at 01:56

answered Feb 19 '19 at 01:38

fifonik

1,556
1
10
18

As already recommended you need to provide your data as you mentioned different data in different queries. Somewhere backstick at the beginning, somewhere -- at the end. This might be the issue when you trying to copy/paste my select query to your data. This is why I provided drop/create/insert + 2 x select. It works on my 5.5.8, 5.7 and mariadb 10.1.14 – fifonik Feb 19 '19 at 01:42
Here is tagname value in my database ~!@#$%^&*()_+|}{":?><./';[]\=-` – Hien Nguyen Feb 19 '19 at 01:49
Updated my answer with your data. – fifonik Feb 19 '19 at 01:51
I tried the query SELECT * FROM `taginfo` WHERE tagname = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`'; but it does not work, can you help me with equal operator? – Hien Nguyen Feb 19 '19 at 02:03
Are you sure that you have exact value your provided? It might be leading or trailing whitespaces that cause 'the issue'. Have you tried to copy/paste my code? Create test database on your server and run the code 'as is'. It returns results for all 2 selects on my servers. – fifonik Feb 19 '19 at 02:16

MySQL server version 10.3.9-MariaDB can not query text has single quote

4 Answers4