[Tutor] Cleaning up input before inserting into MySQL.

[Kent Johnson]

Adam Cripps wrote:
> I have a textarea which collects text and other characters. I'm
> attempting to put this into a MySQL database. See code at [1].
> 
> However, if I use any type of quotes (either single or double) then it
> prematurely closes the SQL statement, and I get an errror:
> 
> ProgrammingError: (1064, "You have an error in your SQL syntax. Check
> the manual that corresponds to your MySQL server version for the right
> syntax to use near 'test'', '44')' at line 1")

You should always pass parameters to cursor.execute() rather than 
building a SQL statement string yourself. Then the database will 
correctly escape any special characters in the values. This has a number 
of benefits:
- It works correctly without any effort on your part to escape quotes, etc
- It protects against SQL injection attacks, where one of the values is 
maliciously constructed to damage the database
- It may be more efficient because the command can be reused with 
different values.

The syntax varies slightly for different databases. In the case of MySQL 
you should use
   mycursor.execute(
   """insert into report (title, content, author) values  %s, %s, %s""",
   (times.getvalue('title'), times.getvalue('content'), 
times.getvalue('pupil'))

Notice that this is *not* a string formatting command! The %s are 
placeholders for MySQL and there is no % formatting operator to combine 
the format with the following tuple.

Kent

> 
> How do I insert these quotes without it closing the SQL statement prematurely?
> TIA
> 
> Adam
> 
> [1]
> def insert(statement):
>     mycursor.execute(statement)
> 
> statement  = """insert into report (title, content, author) values
> ('""" + str(times.getvalue('title')) + """', '""" +
> str(times.getvalue('content')) + """', '""" +
> str(times.getvalue('pupil')) + """')"""
> 
> insert(statement)
> 
> --
> http://www.monkeez.org
> PGP key: 0x7111B833
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> http://mail.python.org/mailman/listinfo/tutor
> 
>