How do I solve this mysql query?

Question

0.00/5 (No votes)

See more:

I have a search engine in my system. When I search using special characters such as @-_.&#() It is working find and I'm getting the result but when I use single quote special character (') I'm not getting the result but there's data in my DB using single quote.

What I have tried:

SQL

SELECT * FROM(SELECT p.PROJECT_ID, p.PROJECT_NAME, p.PHASE, p.STATUS, d.LOGIN_ID AS CREATED_BY,  p.LAST_UPDATED_DATE 
FROM report.GEN_PROJECT p, report.GEN_USER_LOGIN d
WHERE  d.USER_ID = p.CREATED_BY) A 
WHERE  PROJECT_NAME
LIKE '%grammar's%'  ORDER BY PROJECT_NAME  LIMIT 10 OFFSET 0;

Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's %' ORDER BY PROJECT_NAME

Posted 4-Feb-20 14:46pm

Member 12016106

Updated 13-Feb-20 17:47pm

MadMyche

v4

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2020-02-04T16:02:00

Solution 1

Since a single quote is delimiter in sql string, you can't use it inside a string, you need to escape it.

SQL

LIKE '%grammar''s%' ORDER BY PROJECT_NAME LIMIT 10 OFFSET 0;

Posted 4-Feb-20 16:02pm

Patrice T

Comments

Member 12016106 4-Feb-20 22:18pm

Here I'm trying to get the PROJECT NAME from bean:

if(bean.getSearchBean()!=null&&StringUtils.isNotBlank(bean.getSearchBean().getProjectName())) {
conditionSQL = conditionSQL + (StringUtils.isNotBlank(conditionSQL)?" AND ":"") + " PROJECT_NAME LIKE '%"+bean.getSearchBean().getProjectName()+"%'";
}

So,how should I escape it here. Sorry I'm in a learning process. I really appreciate your help.

Patrice T 5-Feb-20 3:55am

Use Improve question to update your question.
So that everyone can pay attention to this information.

Member 12016106 13-Feb-20 23:48pm

Problem solved.. Thank you

Patrice T 14-Feb-20 4:28am

You're welcome.
By the way, you can accept useful solutions, it will close the question.

MadMyche · Answer 2 · 2020-02-04T16:55:00

As Patrice stated, the problem is that the single quote special character is used for letting SQL know that you are dealing with specific types of values, such as text or dates.

If you use an IDE designed for SQL statements, you will notice that Intellisense will change colors when you use the single quotes as well as reserved/special words.
This is even apparent when you place code in this forum; if you notice that your original question has had the SQL changed from bold to code-sql the styles show that where the problem is.

Wendelius · Answer 3 · 2020-02-04T16:59:00

To add the the previous solutions, it looks like the data used in the condition is coming from the user interface.

If that is the case, you should use parameters. One thing is that this way you avoid risk of SQL injection but also this takes away the need to escape the special characters.

For an example, have a look at MySqlCommand.Parameters Property[^]

Member 12016106 · Answer 4 · 2020-02-13T17:47:00

Solution 4

escape single quote by using \'

Posted 13-Feb-20 17:47pm

Member 12016106

Updated 13-Feb-20 17:49pm

How do I solve this mysql query?

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0