[Tutor] postgresql was: Re: Tutor Digest, Vol 115, Issue 6

[Steve Willoughby]

On 04-Sep-2013, at 14:28, Alan Gauld <alan.gauld at btinternet.com> wrote:
> On 03/09/13 08:25, Ismar Sehic wrote:
>> help with postgres and csv:
>> i solved my problem by playing with the sql line a little.
>> it looked like this : sql  = " UPDATE hotel SET path_picture =
>> "+"';"+hotel_url+"'
>>  WHERE code LIKE '"+"%"+hotel_code+"'"
>>  now it's like this : " UPDATE hotel SET path_picture = '" + hot_url +
>> "' WHERE code LIKE '%" + hot_code + "';"
>> 
>> i guess the problem was in building the sql string, but i don't yet
>> quite understand what i did.can someone point me to some online resorces
>> about postgres and python integration?
> 
> https://wiki.python.org/moin/PostgreSQL
> 

While you're looking at all the information Alan pointed you to, consider one other general bit of advice when programming with SQL queries.  It is generally a very convenient trick to use string formatting or string catenation to build the bits of your query from pieces, like you did above ("UPDATE … SET path_picture='" + hot_url + …).

Convenient, but a very, very bad idea in practice.  This makes your program vulnerable to SQL injection, which in many cases can have devastating effects when someone exploits it.  Assuming that the variables come from sources beyond your control (and even if they are--at the moment--generated by you), use parameterized queries (look for those in your API libraries).  They usually look something like the following (although specifics can vary), where you leave a placeholder character like ? in the SQL string, and supply the data values separately.  Unlike using string-maniputation features of Python, the database API knows exactly how to properly include those data values into the SQL command for you:

some_api_function_to_do_sql("UPDATE hotel SET path_picture = ? WHERE code LIKE ?", 
   hot_url, '%' + hot_code + '%')

--steve