Re: [INTERFACES] New code for JDBC driver

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