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

[Sheetal]

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

 

[Steve Holden]

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
curs.execute(stmt)

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.

regards
Steve

 

[Cliff Wells]

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.

You need to either quote the data using whatever quote function the
database module you're using (you didn't say) provides (psycopg, for
example, provides the QuotedString function), or better, just let the
cursor.execute() method do it for you:

cursor.execute("INSERT INTO foo (bar) VALUES (%s)", (input,))

Incidentally, you should *always* make sure data is quoted before going
into the database. Especially user-supplied data. You are flirting
with disaster inserting unquoted data directly from the web. Consider
the following:

input = "'); DROP TABLE foo; --"
cursor.execute("INSERT INTO foo (bar) VALUES ('%s')" % (input,))

It's also possible to bypass username/password checks using similar
techniques.

Regards,
Cliff