Skip site navigation (1) Skip section navigation (2)

Re: [INTERFACES] New code for JDBC driver

From: Barry Lind <barry(at)xythos(dot)com>
To: Arsalan Zaidi <azaidi(at)directi(dot)com>
Cc: PostgreSQL jdbc list <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: [INTERFACES] New code for JDBC driver
Date: 2001-07-03 15:18:03
Message-ID: 3B41E22B.5000901@xythos.com (view raw or flat)
Thread:
Lists: pgsql-interfacespgsql-jdbc
Arsalan,

First your assumption of what is meant by 'escape processing' in the 
JDBC java docs is incorrect.  If you read through the JDBC specs on the 
sun site you will see that escape processing is specifically dealing 
with parsing special constructs that are all of the form:  {keyword 
...parameters...}  (note the requirement for the curly braces).  It has 
nothing to do with quotes, semi-colons or other special characters.

Now to your situation.  The obvious solution is to use prepared 
statements in your middle tier having the client provide the values from 
the text fields to be bound into the query.

 > "update users set name='"+name+"'"

So the client sends you the 'name' value and the middle tier uses a 
preparedstatement to bind that value to the update.

I am assuming that the client program isn't generating the entire SQL 
statement itself and sending that to the middle tier as that would open 
up all sorts of security problems with non-authorized clients sending 
whatever SQL they wanted to the middle tier to execute.


If for some reason you really can't use prepared statements, then it is 
the responsibility of the code that is building the SQL string to 
correctly deal with issues of escaping special characters that might be 
in the value of a variable.

thanks,
--Barry



Arsalan Zaidi wrote:

>>Why do you consider semi colons outside of quotes to be an error
>>
> condition?
> 
>>It is legal under postgresql to execute multiple SQL statements under
>>one call to the server.  For example you can execute:
>>
>>update foo set bar = ?; select * from foo;
>>
> 
> I agree that it probably is. Unfortunately, as I mentioned in my previous
> post,no one is going to be entering in SQL queries directly through our
> interface. We're not doing it and if someone is, he's up to no good. e.g.
> 
> "update users set name='"+name+"'"
> 
> If there's a text box in which you enter your name for the first query, I
> can some fun if I enter in
> 
> Arsalan'';update funds set money = 100000 where userid =10
> 
> Am I right? It's situations like these that I'm trying to prevent. Is there
> a better way?
> 
> The client will not be under our control. We're providing an API in the
> middle which is called by our resellers. We have to perform some checking on
> our side.
> 
> 
>>As to your second point about escapeSQL escaping quotes, I don't see
>>anywhere in the JDBC spec that says that type of behavior is supposed to
>>happen.  Do you have an example where you feel the behavior of the
>>escapeSQL method isn't conforming to the JDBC spec with regards to quote
>>handling?
>>
> 
> In the JDBC docs (from the Sun docs, java.sql -> Statement), it says that
> setEscapeProcessing() will turn on the auto escaping of queries. In the
> code, setEscapeProcessing sets escapeProcessing to true. In executeQuery, if
> escapeProcessing is true, connection.escapeSQL is called. In
> connection.escapeSQL, the string's quotes are *not* escaped. In fact, if I
> remember in an earlier query, all there was, was a lone return sql;
> statement.
> 
> I'm not familiar with the specs, so I could be wrong, but going on the docs
> available to me, it seems to me that connection.escapeSQL should do more
> than it's doing...
> 
> Thanks for listening! :-)
> 
> --Arsalan.
> 
> 
> 



In response to

Responses

pgsql-interfaces by date

Next:From: Manika DeyDate: 2001-07-03 16:07:35
Subject: Re: distinguishing different database connections
Previous:From: Tom LaneDate: 2001-07-03 14:41:59
Subject: Re: [SQL] indexing arrays in pgaccess's query interface is failing

pgsql-jdbc by date

Next:From: Bruce MomjianDate: 2001-07-03 16:41:01
Subject: Re: JDBC Support - prepared Statements?
Previous:From: Barry LindDate: 2001-07-03 15:04:48
Subject: Re: [HACKERS] JDBC Support - prepared Statements?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group