CODEWITHSUNDEEP
javasqljsp
Courtney Sprouse
Courtney Sprouse

Reputation: 43

Concatenating a SQL string query in a JSP file

I have a html page that allows users to enter specific search terms to query the database I've created. The problem I'm having is that when I pass the string to execute as a sql query, it is not wrapping the query in single quotes which is needed to search for a string match in a sql database. Here is the code I have currently:

//Create a statement for the sql queries  
java.sql.Statement stmt = conn.createStatement();

//Get results from queries entered  
String result_event_name = request.getParameter("event_name");

//Create query string   
String sqlQuery_event  = "SELECT event_name FROM event where event_name = " + "\'" + result_event_name + "\'";

//execute query  
java.sql.ResultSet rs_event = stmt.executeQuery(sqlQuery_event);`

This is the error I get:

SQLException: ERROR: syntax error at end of input Position: 49

I tried using prepare statement -- returns same error
I tried the query without escaping -- returns same error
I tried with no single quotes -- returns same error

Upvotes: 0

Views: 891

Answers (1)

SkyWalker
SkyWalker

Reputation: 29150

  1. The single quote ' only needs escaping once LIKE '%\'%'
  2. But to query backslash \ you need to double escape to LIKE '%\\\\%'
  3. If you wanted to query backslash+singlequote \' then LIKE '%\\\\\'%' (with 5 backslashes)

Explanation Source excerpt:

Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\n”. To search for “\”, specify it as “\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

Credit goes to xchiltonx

Resource Link:

mysql - How to handle query search with special characters /(forward slash) and \(backslash)

Upvotes: 1

Related Questions