Creating SQL queries with UNICODE or ASCII character codes

Question

I would like to create a SQL query containing ASCII or UNICODE character codes in it. For example, ASCII character code for single quote (') is 39 and unicode code is U+0027. In Java, I would like to write a query by replacing the single codes with their character codes:

ASCII:

connection.createStatement().executeQuery("select * from users where name =39test39")

Unicode:

connection.createStatement().executeQuery("select * from users where name =U+0027testU+0027")

All of these queries should be equivalent to "select * from users where name ='test'"

When I run the codes above, DBMS (I tried with Mysql and SQLite) does not recognize the ascii and unicode codes as a single quote.

In summary, I know parametrized queries are the ideal. But, here in this case what I wanted to do is, when the sql code is parsed by the DBMS, then the DBMS should recognize the unicode character. For example, if I use \u0027, the JVM would recognize this as a single quote, but I want JVM to not recognize and DMBS to recognize the character encoding.

Is there any way use char codes instead of the character itself?

There is a difference of a single quote being part of the statement (per syntax) or part of the data. Regarding the statement you have to use a single quote as it is to enclose character literals. So no, your queries aren't equivalent, the first two are just wrong in SQL. But of course you can build a query in Java using the codes. — sticky bit
– sticky bit, Commented Sep 24, 2018 at 12:21
didn't understand the problem, something like "select ... name = " + (char) 39 + "test" + (char) 39 or "select ... name = \u0027test ..." ? but why, its the same as writting the string ? and it would be better to use PreparedStatement with placeholders — user85421
– user85421, Commented Sep 24, 2018 at 12:26
Hmmm.. no, you probably don't want to do any of this. What you actually want to do is head to bobby-tables.com and pick up some tips for parameterizing your queries so you can put whatever you like in as variable text without it mashing the query up.. — Caius Jard
– Caius Jard, Commented Sep 24, 2018 at 12:26
Yes, I know parametrized queries are the ideal. But, what I wanted to do was, when the sql code is parsed by the DBMS, then the DBMS should recognize the unicode character. For example, if I use \u0027, the JVM would recognize this as a single quote, but I want JVM to not recognize and DMBS to recognize the character encoding. — cagryInside
– cagryInside, Commented Sep 24, 2018 at 12:36
I do not think that SQL is specified to allow anything but the ' character at that position ... for expressions the DBMS may provide some function for that (like CHAR() - select char(65) ... or ... where letter = char(65)) — user85421
– user85421, Commented Sep 24, 2018 at 12:42

Gord Thompson · Accepted Answer · 2018-09-24 12:57:07Z

4

No, you don't want to do that. You should be doing

PreparedStatement ps = conn.prepareStatement("select * from users where name = ?");
ps.setString(1, "test");
ResultSet rs = ps.executeQuery();

Remember that all strings in Java are Unicode strings, so what you are proposing is to start sending string values as byte streams to the JDBC driver, which would be messy and error-prone (if it is even possible).

edited Sep 24, 2018 at 12:57

answered Sep 24, 2018 at 12:40

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Kalupa · Accepted Answer · 2018-09-24 12:28:55Z

0

When you put the ascii/unicode numbers within double quotes they aren't resolved to characters instead try something like:

"select * from users where name =" + Character.toString(Character.toChar(yourIntHere)) + ...

And then that should build the string you are looking for

answered Sep 24, 2018 at 12:28

John Kalupa

11 bronze badge

1 Comment

user85421 Over a year ago

note: to convert an int its just (char) value, no need to create a String - and there is no toChar(int) in Character (Java 10)

Anuja Barve · Accepted Answer · 2018-09-24 12:49:47Z

0

You query should look like this :

"select * from users where name =" + Character.toString((char)39) + "test" + Character.toString((char)39) + "\""

answered Sep 24, 2018 at 12:49

Anuja Barve

3202 gold badges7 silver badges28 bronze badges

Collectives™ on Stack Overflow

Creating SQL queries with UNICODE or ASCII character codes

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related