python save sql INSERT statements to file . singlequotes around values colliding with single quotes present in the value being inserted

Question

I am using python to save a blogdata as an sql file .

str = "INSERT INTO TABLENAME (id,name,con) VALUES ('%d','%s','%s')" %(id,nam,con)

Here, "con" refers to the content which contains all kinds of things like single quotes , double quotes and others . So the file became like this :

INSERT INTO TABLENAME (id,name,con) VALUES('1','first','Hi I'm XXX . "435"  'sdfsd' ')

Please observer last part of above line, as it contains different quotes, the sql file will not be executed if i use the above format . What is the solution for this ? Will provide more information if necessary .

Thanks

This type of string formatting ( with `%` ) is too dangerous for sql. SQL injection is easy this way. What if con contains `');delete from table;` ? — Marco de Wit, Jul 04 '12 at 12:23
Even it contains dangerous elements , its not problem here, but the requirement is , it shouldnot give errors . It should work normally . — user1434480, Jul 04 '12 at 12:29

score 1 · Answer 1 · answered Jul 04 '12 at 12:42

psycopg2 has the method mogrify outputting a safe SQL query as string:

>>> print cur.mogrify("insert into table_name (id, name, con) values (%s, %s, %s)", ('1','first',"Hi I'm XXX"))

insert into table_name (id, name, con) values (E'1', E'first', E'Hi I''m XXX')

Check your DB module for this method.

score 1 · Accepted Answer · edited May 23 '17 at 10:29

1

What you want to do is sanitize your queries, to avoid having sql injections and having valid queries. In order to do this, you need to escape literal values, like strings. There are libraries for this. In your case, and that's the least safe option, you could use repr for strings, which will do the job. There are better options, DON'T USE THIS, use what your database drivers offer you. For example:

If you are using MySQL, you could use python-mysql (MySQLdb) to escape your strings, so that you can generate sql files which you can execute safely.

>>> print a
Hi I'm XXX . "435"  'sdfsd' 
>>> import MySQLdb
>>> encoded_a = MySQLdb.escape_string(a)
>>> print encoded_a
Hi I\'m XXX . \"435\"  \'sdfsd\'

When using MySQLdb.escape_string, you are sure the characters are safe, then you use the format string you were using. Also, make sure you do this for ALL values, and not only those who are "risky".

If you are not using MySQL, check this out for postgresql.

If you do not want to use it directly via SQL, look into libraries like MySQLdb for MySQL, etc.

edited May 23 '17 at 10:29

Community

1
1

answered Jul 04 '12 at 12:46

jadkik94

7,000
2
30
39

[Here](http://mysql-python.sourceforge.net/MySQLdb.html#installation). Check the [README](http://mysql-python.svn.sourceforge.net/viewvc/mysql-python/trunk/MySQLdb/README?revision=650&view=markup). Tell me what OS, and I'll tell you how to install it. – jadkik94 Jul 04 '12 at 13:01
its ubuntu, and thanks for answer with clear explanation . Thanks to others also . i tried sudo apt-get install python-mysql , installed but import statement didnt work . – user1434480 Jul 04 '12 at 13:05
`sudo apt-get install python-mysql` should do the job. You're welcome :) – jadkik94 Jul 04 '12 at 13:06
Did you restart the python interpreter? And also make sure that you write it correctly (it's case-sensitive) – jadkik94 Jul 04 '12 at 13:07

python save sql INSERT statements to file . singlequotes around values colliding with single quotes present in the value being inserted

2 Answers2