Re: LIKE predicate and '\' character - Mailing list pgsql-hackers

From Andy Hallam
Subject Re: LIKE predicate and '\' character
Date
Msg-id 9sbb7b$sbl$1@news.tht.net
Whole thread Raw
In response to LIKE predicate and '\' character  ("Andy Hallam" <ahm@exel.co.uk>)
List pgsql-hackers
> I sent a previous mail with regard to using the '\' (backslash) character
in
> an SQL SELECT statement.
> The outcome was that postgres does not escape the '\' itself - I need to
do
> it myself before submitting the SQL - fair enough, I now do this.

I have just written a java app that uses a single '\' in an SQL SELECT
statement and unlike my C application that uses the PSQLODBC driver this
*DOES* return data. To me this says that the problem of having to escape the
'\' myself (as I have to do in my C++ ODBC application) has already been
addressed in the Java driver, and so I do not need to escape it myself in my
Java application.
If this problem has been addressed in the Java driver then surely (for
conformity) it should also be addressed in the ODBC driver ?.


Here is my Java code :
...       try {               con = DriverManager.getConnection(url, "postgres",
"postgres");       }       catch (Exception e) {           MyOutput(e.getMessage());           System.exit(1);       }
       try {          String strPart;
           strPart = "A\\B";
MyOutput("strPart: <" + strPart + ">");
           strSQL = "SELECT partdesc FROM partmaster WHERE partnum = ?";
MyOutput("SELECT SQL: <" + strSQL + ">");
           PreparedStatement pstmt = con.prepareStatement(strSQL);           pstmt.setString(1, strPart);
           result = pstmt.executeQuery();
           while (result.next()) {               data = result.getString(1);
MyOutput("DATA FETCHED: Partdesc = <" + result.getString(1) + ">");           }       }       catch (Exception e) {
     MyOutput(e.getMessage());           System.exit(1);       }
 

Here is my program output:

strPart: <A\B>
SELECT SQL: <SELECT partdesc FROM partmaster WHERE partnum = ?>
DATA FETCHED: Partdesc = <AB SLASH TEST>


Java does have the same problem with the LIKE predicate however, as to
return any data I need to change my code to :

...
strPart = "A\\\\B";
...
strSQL = "SELECT partdesc FROM partmaster WHERE partnum LIKE ?";

Comments please.

Andy
ahm@exel.co.uk

"Andy Hallam" <ahm@exel.co.uk> wrote in message
news:9sb3ek$r0k$1@news.tht.net...
> PostgreSQL - 7.1.3  (installed on Linux 2.4.2-2)
> PSQLODBC.DLL - 07.01.0007
> Visual C++ - 6.0
>
> I sent a previous mail with regard to using the '\' (backslash) character
in
> an SQL SELECT statement.
> The outcome was that postgres does not escape the '\' itself - I need to
do
> it myself before submitting the SQL - fair enough, I now do this.
>
> i.e
> instead of
>     mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\me';
> I now do
>     mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\\me';
>
> BUT, if I use the LIKE predicate I have to escape the escape.
>
> i.e
>     mydb=# SELECT * FROM users WHERE id LIKE 'WORKGROUP\\\\me';
>
>
> Now this must be treated as a bug.
> As you can see it is not an error with the PSQLODBC driver as I ran the
SQL
> from the command line with the same results.
> I am presuming that the backend parsing logic around the LIKE prodicate is
> ignoring the '\'.
>
> Is anyone working on this ?. Can anyone send me a fix, as without this I'm
> screwed.
>
> Thanks for any help
>
> Andy.
> ahm@exel.co.uk
>
>
>






pgsql-hackers by date:

Previous
From: Stefan Rindeskar
Date:
Subject: Re: Storage Location Patch Proposal for V7.3
Next
From: Kevin Jacobs
Date:
Subject: 7.2 Beta2 bug report