[Tutor] Cleaning up input before inserting into MySQL.
Kent Johnson
kent37 at tds.net
Sat Mar 25 12:46:56 CET 2006
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
>
>
More information about the Tutor
mailing list