Pre-defined function to format strings in Python to be used in SQL

Question

First of all i'm French so if you see any mistakes in my message, don't hesitate to tell me ! :)

Here's my problematic : I'm currently making a Python function, to build SQL requests, inserting in my database a large string (the HTML code to a webpage).

As you may know, SQL have some requirements regarding the string you use. For example you can't use ' I'm a developper' sentence because of the ' ' ' thing.

Do you know if any pre-defined function exist to modify a chain to make sure I could use it in an SQL Request ?

I'm also asking that because I don't know all the requirements to a string to be interpreted without a problem.

I hope I've been clear, let me know if I havent ! Have a nice day.

EDIT : I'm on Python 3.4.3 EDIT2: I'm using MySQL

Answer 1

This is a pretty broad question and I think you should refine it.

As for good practices with SQL in general you first need to :

• Make sure you are using the proper library (and the most up to date) depending or your situation (MySQL, Oracle, SQLite, etc.)

• Escape string. The library usually has a function for this. You can also use format or %.

• Test, unit tests or at least manual one... What I usually do to prototype is to first generate the SQL statement and print it. Then I copy this statement and test it directly into SQL command line. This way you're sure it's working for SQL. Then from there, you'll need to escape special characters with \. Second points would cover that though.

• Finally you want to wrap your code in a try / catch statement so you can control failures.

• If it still does not work, a good, somewhat hacky, .replace() will. An example would be: .replace("'", "\'"). Make sure you can standardized your input.

Edit: The length of your statement does not matter if you cover edge cases.

Edit 2 : Use MySQLdb.escape_string(statement) if you're using mysql-python https://dev.mysql.com/doc/connector-python/en/connector-python-api-cext-escape-string.html