sql parser exception when with double quotation marks

[maitong]

Describe the bug A clear and concise description of what the bug is.

To Reproduce Steps to reproduce the behavior:

1. Example SQL SELECT id,account,real_name,mobile,email,mobile_account,created_at,status,merchant_type,type FROM user WHERE deleted = 0 and account like "%"'bbc'"%" and merchant_id = 182979 and type = 2 and id != 25000167979 order by created_at desc;

2. Parsing this SQL using JSqlParser with this statements CCJSqlParserUtil.parse(String.class);

3. Exception Was expecting one of:

   "&" "&&" "::" ";" "<<" ">>" "AND" "AT" "COLLATE" "CONNECT" "ESCAPE" "FOR" "GROUP" "HAVING" "OR" "ORDER" "START" "XOR" "[" "^" "|" <EOF>

   at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:190) at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:63) at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:38) Expected behavior A clear and concise description of what you expected to happen.

System

• mysql 5.7.17
• Java 1.8.0_221
• JSqlParser 4.2

[manticore-projects]

Your quotes are wrong. This one would be correct

SELECT  id
        , account
        , real_name
        , mobile
        , email
        , mobile_account
        , created_at
        , status
        , merchant_type
        , type
FROM user
WHERE deleted = 0
    AND account LIKE '%\"bbc\"%'
    AND merchant_id = 182979
    AND type = 2
    AND id != 25000167979
ORDER BY created_at DESC
;

Test it here online and interactively.

Please close after confirmation.

[maitong]

Your quotes are wrong. This one would be correct

SELECT  id
        , account
        , real_name
        , mobile
        , email
        , mobile_account
        , created_at
        , status
        , merchant_type
        , type
FROM user
WHERE deleted = 0
    AND account LIKE '%\"bbc\"%'
    AND merchant_id = 182979
    AND type = 2
    AND id != 25000167979
ORDER BY created_at DESC
;

Test it here online and interactively.

Please close after confirmation.

yes，this would be correct when use like '%"bbc"%'， like "%"'bbc'"%" be supported when use mybatis or navicat premium，me want to use in mybatis interceptor parser this sql

[manticore-projects]

You will need to mangle your SQL before sending it to JSQLParser and replace the MyBatis Quotes with proper SQL compliant quotes. There is no other solution available (at the moment).

[maitong]

You will need to mangle your SQL before sending it to JSQLParser and replace the MyBatis Quotes with proper SQL compliant quotes. There is no other solution available (at the moment).

i get it, thanks

[ChenZheOnePiece]

SELECT
	id,
	flag,
	created_by,
	created_date,
	modified_by,
	modified_date,
	address,
	avatar,
	avatar_content,
	birthday,
	blog,
	email,
	gender,
	id_number,
	integral,
	login_name,
	nickname,
	phone_num,
	remark,
	state,
	tag,
	user_index,
	user_name,
	experience,
	balance 
FROM
	user_info 
WHERE
	flag = 1 
	AND login_name LIKE "%" ? "%"

How to write SQL like this

[wumpz]

You could use the concat function of mysql. This has the advantage to be implemented in multiple database types.

select
*
from user_info
where flag = 1 and login_name like concat('%', ?, '%')

[ChenZheOnePiece]

SQL needs to be compatible with Oracle MySQL PG ,if in xml "%"#{name}"%" It is universal .Is there any other way to have this effect

[wumpz]

Double quotation marks are not allowed there for Oracle and Postgresql. concat is a two parameter function in Oracle, therefore:

concat(concat('%', ?), '%')

should do the trick.

[manticore-projects]

Can we close this as answered and explained?

[wumpz]

Since there is not action for over a year ...