[Tutor] help with data insert into Access table

[Mark Lawrence]

On 29/01/2014 16:46, Peter Otten wrote:
> Ahmed, Shakir wrote:
>
>> I am trying to insert a record in the access table, the value has a quote
>> and could not insert the record. Any idea how I can insert records like
>> this quotes.
>
>> cursor.execute("INSERT INTO PicsPostInfo(Pics_name) values ('Site Name's
>> Harbor.JPG')") Traceback (most recent call last):
>>    File "<interactive input>", line 1, in <module>
>> ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access
>> Driver] Syntax error (missing operator) in query expression ''Site Name's
>> Harbor.JPG')'. (-3100) (SQLExecDirectW)")
>
> Every compliant database module has a paramstyle attribute, e. g. for
> sqlite3:
>
>>>> import sqlite3
>>>> sqlite3.paramstyle
> 'qmark'
>
> "qmark" means that you use "?" instead of the actual value.
> http://www.python.org/dev/peps/pep-0249/ has a list of available
> `paramstyle`s.
>
> Assuming that the database driver you are using uses "qmark" your code would
> become
>
> cursor.execute("INSERT INTO PicsPostInfo(Pics_name) VALUES (?)",
>                 ("Site Name's Harbor.JPG",))
>
> i. e. in addition to the SQL statement there is a tuple (in this case a 1-
> tuple, the trailing comma is necessary!) holding the values. This way is the
> only reasonable way to go when the actual data is provided by your users
> because it prevents SQL injection attacks.
>
> See also http://xkcd.com/327/
>

I think it's worth pointing out that there is a difference here between 
the OP's 'Site Name's Harbor.JPG' and Peter's "Site Name's Harbor.JPG". 
  Left as homework for the newbies :)

-- 
My fellow Pythonistas, ask not what our language can do for you, ask 
what you can do for our language.

Mark Lawrence