Page 1 of 2 12 LastLast
Results 1 to 20 of 23
  1. #1
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default PreparedStatements and escaped text

    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.

  2. #2
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    What is currently being inserted by PreparedStatement?

  3. #3
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

    Quote Originally Posted by masijade View Post
    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.

  4. #4
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

    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.

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,945
    Rep Power
    19

    Default

    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?
    Last edited by Tolls; 03-03-2011 at 12:24 PM.

  6. #6
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

    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.

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,945
    Rep Power
    19

    Default

    I would be surprised, I have to say.

    And it would also be driver dependent...

  8. #8
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

    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?

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,945
    Rep Power
    19

    Default

    Again, which driver are you using?
    Which db?

  10. #10
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

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

    driver = JDBC5.1

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,945
    Rep Power
    19

    Default

    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.

  12. #12
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,945
    Rep Power
    19

    Default

    What is JDBC 5.1?

  13. #13
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    What is JDBC 5.1?
    Java Database Connector version 5.1... you know... from Sun/Oracle?

  14. #14
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,945
    Rep Power
    19

    Default

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

    The thing that actually does the work.

  15. #15
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

    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 '\'?

  16. #16
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    No, I mean what driver are you using?
    MySQL connector?

    The thing that actually does the work.
    I thought the JDBC contained the driver?

  17. #17
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

    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.

  18. #18
    Anupesh is offline Member
    Join Date
    Mar 2011
    Posts
    1
    Rep Power
    0

    Default Why we create the Dynamic Object in java ???

    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 ..

  19. #19
    porchrat is offline Senior Member
    Join Date
    Mar 2009
    Posts
    105
    Rep Power
    0

    Default

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

  20. #20
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,945
    Rep Power
    19

    Default

    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.

Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 0
    Last Post: 12-09-2010, 05:56 PM
  2. Replies: 0
    Last Post: 09-28-2010, 09:52 AM
  3. PreparedStatements
    By lompic in forum New To Java
    Replies: 0
    Last Post: 04-14-2010, 12:59 PM
  4. BinaryStream with PreparedStatements
    By atom86 in forum Advanced Java
    Replies: 3
    Last Post: 10-21-2009, 11:54 AM
  5. Replies: 2
    Last Post: 01-23-2009, 04:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •