form gives error when user submit "'" (single quote) in the form

Steve Holden steve at
Wed Oct 20 14:07:31 CEST 2004

Sheetal wrote:

> Hello
> I have a text area form element in my HTML form. When a user enters
> the "'" in the form the form throws an error when i use the value to
> enter in the database.
> Please help

Your code is also taking the risk that the user will enter a string that 
completely alters the intent of your SQL code, so you need to clean this 
up. I presume you are using some DB API-compliant module, in which case 
you should use parameterized queries rather than building them yourself 
from strings.

The commonest problem is using something like

stmt = "INSERT INTO tbl (ColumnName) VALUES('%s')" % FormValue

to construct a SQL statement and insert data into a table. Instead you 
should use the parameterisation abilities of your database module (but 
be careful: different modules have different paramstyles, so some use 
question marks, others use different parameter markers), and use

stmt = "INSERT INTO tbl (ColumnName) VALUES(?)"
curs.execute(stmt, (FormValue, ))

When a second argument is given to execute it's expected to be a tuple 
of required values - in this case there's just one, but the principle 
extends. This construct has the datab ase module perform any necessary 
escaping of awkward characters liek quotes, and makes things more 
readable too.

Obviously you could do the escaping yourself with something like

stmt = "INSERT INTO tbl (ColumnName) VALUES('%s')" % \
		FormValue.replace("'", "''")

but this gets old quickly, and can trip you up if you decide to port to 
another database engine with different quoting requirements.

Holden Web LLC +1 800 494 3119

More information about the Python-list mailing list