Literal Escaped Octets

[Chason Hayes]

On Wed, 08 Feb 2006 00:57:45 -0500, Steve Holden wrote:

> Chason Hayes wrote:
>> On Tue, 07 Feb 2006 01:58:00 +0000, Steve Holden wrote:
>> 
>> 
>>>Chason Hayes wrote:
>>>
>>>>On Mon, 06 Feb 2006 13:39:17 +0000, Steve Holden wrote:
>>>
>>>[...]
>>>
>>>>>The URL you reference is discussing how you represent arbitrary values 
>>>>>in string literals. If you already have the data in a Python string the 
>>>>>best advise is to use a parameterized query - that way your Python DB 
>>>>>API module will do the escaping for you!
>>>>>
>>>>>regards
>>>>> Steve
>>>>
>>>>
>>>>Thanks for the input. I tried that with a format string and a
>>>>dictionary, but I still received a database error indicating illegal
>>>>string values. This error went away completely when I used a test file
>>>>consisting only of text, but reproduced everytime with a true binary file.
>>>>If you can let me know where I am wrong or show me a code snippet with a
>>>>sql insert that contains a variable with raw binary data that works,
>>>>I would greatly appreciate it.
>>>>
>>>
>>>I tried and my experience was exactly the same, which made me think less 
>>>of PostgreSQL.
>>>
>>>They don't seem to implement the SQL BLOB type properly, so it looks as 
>>>though that rebarbative syntax with all the backslashes is necessary. Sorry.
>>>
>>>regards
>>>  Steve
>> 
>> 
>> with regards to escaping data parameters I have found that I have to
>> specifically add quotes to my strings for them to be understood by
>> pstgresql. For example
>> 
>> ifs=open("binarydatafile","r")
>> binarydata=ifs.read()
>> stringdata=base64.encodestring(binarydata)
>> 
>> #does not work
>> cursor.execute("insert into binarytable values(%s)" % stringdata)
>> 
>> #need to do this first
>> newstringdata = "'" + stringdata + "'"
>> 
>> then the select statment works.
>> Is this expected behavior? Is there a better way of doing this?
>> 
>> thanks for any insight
> 
> Yes, parameterize your queries. I assume you are using psycopg or 
> something similar to create the database connection (i.e. I something 
> that expects the "%s" parameter style - there are other options, but we 
> needn't discuss them here).
> 
> The magic incantation you seek is:
> 
> cursor.execute("insert into binarytable values(%s)", (stringdata, ))
> 
> Note that here there are TWO arguments to the .execute() method. The 
> first is a parameterized SQL statement, and the second is a tuple of 
> data items, one for each parameter mark in the SQL.
> 
> Using this technique all necessary quoting (and even data conversion 
> with a good database module) is performed inside the database driver, 
> meaning  (among other things) that your program is no longer vulnerable 
> to the dreaded SQL injection errors.
> 
> This is the technique I was hoping would work with the bytea datatype, 
> but alas it doesn't. ISTM that PostgreSQL needs a bit of work there, 
> even though it is otherwise a very polished product.
> 
> regards
>   Steve

That was it. Thanks for your great help.

Chason