PreparedStatements and escaped text

[porchrat]

Hi all

I'm having a bit of a problem. It seems that the PreparedStatement.setString(int, String) method doesn't recognise escaped characters in text.

I was hoping to find some sort of other method in PreparedStatement that recognises the escaped characters. Maybe there is one in String but I can't see it either :o

I have a String containing 2 '\' characters in a row (e.g. "foo\\bar"). Obviously the first '\' escapes the second and with a normal Statement object this sort of String is recognised for what it is and the first '\' is removed (so "foo\bar" is inserted). Is there a way to be able to successfully pass that String through PreparedStatement.setString(int, String)?

I really don't want to have to go back to ordinary Statements.

[masijade]

What is currently being inserted by PreparedStatement?

[porchrat]

What is currently being inserted by PreparedStatement?

"foo\\bar" is currently being inserted. I want "foo\bar". Obviously I can't store a String as "foo\bar" because it is improper escape text.

[porchrat]

I just checked it on quote marks (foo\"bar\") and the PreparedStatement inserts it as intended: foo"bar"

Maybe it just doesn't work for "\\". Which is a problem.

[Tolls]

Are you hardcoding the String?
Because if "foo\bar" is coming from, say, a client then that shouldn't be a problem.

ETA: In fact, which db and which driver are you using?

[porchrat]

Wait wait I think the problem might be with my own code. I have some methods that are supposed to be removing the escapes and it looks like they don't do it. Looks like it might just be my idiocy. Which is embarrassing but a relief. :o

I'll need to look into it but I will keep the thread open to let you guys know if PreparedStatement actually is the problem. I certainly hope not.

[Tolls]

I would be surprised, I have to say.

And it would also be driver dependent...

[porchrat]

Yea the Statement definitely inserts differently to the PreparedStatement. Even when I don't use my own methods and just setup a simple straight PreparedStatement and a Statement they produce different results for the same String.

For example:

If I give it "foo\\\\bar" the PreparedStatement ends up inserting: "foo\\bar" (or at least that is what shows up when I query the table)

For the same String Statement ends up inserting "foo\bar".

This is totally nuts. Perhaps it has something to do with the fact that "foo\\\\bar" is cobbled together into a larger INSERT statement String?

[Tolls]

Again, which driver are you using?
Which db?

[porchrat]

db = MySQL (5.1 I think but I'll check) EDIT: yes definitely 5.1

driver = JDBC5.1

[Tolls]

Also, the Statement will be taking:
"INSERT INTO tbl ("foo\\\\bar")"

This will be turned into SQL:
INSERT INTO tbl ("foo\\bar")

Which, depending on you db (this applies to MySQL) as:
INSERT INTO tbl ("foo\bar")

Which is correct.

For the PreparedStatement:
ps.setString(1, "foo\\\\bar");

Will result in:
INSERT INTO tbl ("foo\\bar")
since the slashes ('\\' is a single slash remember, so the 4 will result in 2) will be escaped.

[Tolls]

What is JDBC 5.1?

[porchrat]

What is JDBC 5.1?

Java Database Connector version 5.1... you know... from Sun/Oracle?

[Tolls]

No, I mean what driver are you using?
MySQL connector?

The thing that actually does the work.

[porchrat]

So you're saying MySQL does it's own escape editing when it receives the String? So essentially when I use an ordinary Statement object the String with 4 '\' characters gets processed for escaped characters not once but twice? (the first processing in Java drops it to 2 then the second processing in MySQL drops it to 1)

But for the PreparedStatement only Java does escaped text processing? So it only drops to 2 '\'?

[porchrat]

No, I mean what driver are you using?
MySQL connector?

The thing that actually does the work.

I thought the JDBC contained the driver?

[porchrat]

Hmm I see what you mean. JDBC is a broad specification for connectors.

I checked up on it and it is MySQL Connector/J.

Sorry for the confusion.

[Anupesh]

please anybody tell me about the full specification of that why we use this terminology in java..
even that we can create the object statically in c language ..and it is more memory using than dynamic created objects ....


Please Specify the answers ..

[porchrat]

Uh... maybe make a thread for it? ... you know... your own thread...

[Tolls]

All JDBC does is send the SQL statement down as it should appear (after escaping is taken into account).
Since '\' is the escape character for MySQL then anything arriving with that in will be escaped itself.

Using setString result in the PreparedStatement escaping what you provide.

So the Statement '\\\\' becomes '\\' for the SQL and then '\' in the database.
For the PreparedStatement the '\\\\' becomes '\\' for the SQL, which the PreparedStatement will escape back to '\\\\' which results in '\\' in the database.

Because if someone enters "some data I really want entered including all the slashes \\\\" into a field on whatever front end they're using, they will expect to see that in the db. With PreparedStatement they will get that, but with a Statement they'll only get two slashes.