Re: SQL parsing irregularity when using SQL reader syntax.

When I run the same query against the POSTGRESQL-SOCKET3-DATABASE 
backend (which utilizes cl-postgres and a socket interface to postgres) 
I get the following message.

While accessing database
#<POSTGRESQL-SOCKET3-DATABASE progden/trac/trac OPEN {100EC9D263}>
   with expression "SELECT FOO FROM BAR WHERE (FOO = '^@')":
   Error DATABASE-ERROR / insufficient data left in message
   has occurred.

I am not sure what the answer is here.  Is this a security issue? It 
seems like the errors are getting thrown up at the command parser (as 
invalid sql).  I suppose we could add code to error on invalid 
characters (what is a list of these, seems hard to determine, especially 
in light of binary fields?).  This is potentially expensive, but perhaps 
worth it.

This is only related to the syntax in that the syntax will happily 
splice that string together for you.  Manually splicing the string and 
sending it to clsql would yield similar results. (The 
clsql-sys:sql-escape-quotes only escapes quotes.)

What about binary fields where nul is a valid character? Should we be 
replacing nul with its escape sequence (what about EOT, EOF etc)?

----

Alternatively if you use the socket3 backend, you can utilize command 
objects and pass these strings as parameters as follows. (This requires 
installing cl-postgres which is the backend of postmodern):

(let ((my-string (make-string 1 :initial-element #\Nul)))
    (clsql:select [foo] :from [bar] :where [= [foo] [$1]] :parameters 
(list my-string)))

Cheers,
Russ


On 11/5/2012 7:13 PM, J Phelps wrote:
> Platform: Postgres/Linux
>
> Reproduction:
>
> (let ((my-string (make-string 1 :initial-element #\Nul)))
>     (select [foo] :from [bar] :where [= [foo] my-string]))
>
> You should get an error about an unterminated string constant, caused by the nul
> character eventually being treated as a C-string terminating nul. Discovered by
> a hacker who was trying to SQL-inject me.
> _______________________________________________
> CLSQL mailing list
> [email protected]
> http://lists.b9.com/cgi-bin/mailman/listinfo/clsql