Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I escape the colon (:) in a mysql query over jdbc that contains a variable assignment?

I'm trying to run a query that involves a custom variable over JDBC toward a MySQL 5.0 database (Hibernate 4 as the ORM):

SET @rownum := 0; SELECT rnum FROM (
    SELECT (@rownum := @rownum + 1) AS rnum, col_name_a
    FROM table_name
    WHERE (col_name_b IS NULL  OR col_name_b != 'USER' )  ORDER BY col_name_a) c
WHERE col_name_a = :aValue

The first SET @rownum := 0; is needed to reset the variable, but the colon isn't required there (I've set the allowMutilQuery=true as suggested in this question).

The problem is with the following SELECT, where I keep on getting:

org.hibernate.QueryException: Space is not allowed after parameter prefix ':'

Is there a way around this? Should I use other methods than

javax.persistence.Query q = EntityManager instance>.createNativequery(String)

and

q.setParameter(<param name>, <param value>)

?

like image 929
watery Avatar asked Jan 21 '14 16:01

watery


People also ask

How do you escape a semicolon in MySQL?

Heredoc syntax is simply another way to delimit a string literal, it does not "escape" anything. In order to escape the string (to prevent SQL-injection) you need to either call mysqli_real_escape_string() , or (preferably) use parameterized queries.

How do I escape a comma in MySQL?

Special characters such as commas and quotes must be "escaped," which means you place a backslash in front of the character to insert the character as a part of the MySQL string.


1 Answers

In hibernate the escape of the colon : is done with a backslash:

SET @rownum \:= 0; SELECT rnum FROM ( ...

Or in java:

String sql = "SET @rownum \\:= 0; SELECT rnum FROM ( ...";
like image 70
Joop Eggen Avatar answered Nov 06 '22 20:11

Joop Eggen