mysqlsqlmariadbtoadmariasql

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

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: enter image description here

Here is result query without single quote: enter image description here

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

Solution

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