Python inserting html into MySQL

Question

I am trying to insert HTML code into a field in a MySQL db.

I use the following code to do it

cur.execute("INSERT INTO `table1`(field1) VALUES("+ str(data) +")")

But I get the following error:

_mysql_exceptions.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 \'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 
Transitional//EN" "http://www.w3.or\' at line 3')

How can I solve this problem? Thanks in advance!

a) what does data look like, b) please please please don't use string concatenation to create SQL queries, use prepared statements or an ORM — Daniel DiPaolo
– Daniel DiPaolo, Commented Aug 15, 2012 at 15:41
@DanielDiPaolo - I see it as less clear cut. Either you sanitise your SQL or you never ever let anyone ever use your code. — Joe
– Joe, Commented Aug 15, 2012 at 20:11

Daniil Ryzhkov · Accepted Answer · 2012-08-16 12:05:22Z

4

You should escape your string:

cur.execute("INSERT INTO `table1`(field1) VALUES('"+ conn.escape_string(str(data))+"')")

Where conn is your connection.

edited Aug 16, 2012 at 12:05

answered Aug 15, 2012 at 15:42

Daniil Ryzhkov

7,6264 gold badges44 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ahmadux Over a year ago

I tried the following, however I got the error that conn. is not defined,so I changed it to mysqldb and it said that: code_mysql_exceptions.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 \'\\r\\n\\r\\n<!DOCTYPE HTML PUBLIC \\"-//W3C//DTD HTML 4.01 Transitional//EN\\" \\"http:\' at line 1') code

Daniil Ryzhkov Over a year ago

Can you post generated query? Also try to use this kind of brackets: '

ahmadux Over a year ago

The code works! Thanks! But I can seem to only select data, but it's not inserting. When I start a new .py file and use the same code to insert and it works. Any ideas?

ahmadux Over a year ago

Used: con.commit(). Works perfect now! Thanks for all the help!

RocketDonkey · Accepted Answer · 2012-08-15 15:42:04Z

1

Check your string - it looks like you are escaping single quotes but leaving double quotes un-escaped (and since you're enclosing with double-quotes, the string closes 'early').

answered Aug 15, 2012 at 15:42

RocketDonkey

37.4k8 gold badges83 silver badges84 bronze badges

Collectives™ on Stack Overflow

Python inserting html into MySQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related