Escape Query for MySQL in Python

Coders must use escape characters to make sure their code is hack proof.

Python, a programming language equipped with dynamic semantics, is a favorite among programmers and coders, but so too is MySQL. Integration of MySQL with Python often isn’t much of a problem for experienced programmers, but coding may get a little convoluted because of C API to _mysql function mapping. Escaping is one of these problems.

MySQLdb

For the Python database API, MySQLdb is often the weapon of choice. Coders can avoid direct write to module by using the MySQL interface. This enables the implementation of MySQL C API, but this may not always be a straightforward task. MySQL C API has its own object-oriented processes. When working with escape functions, "mysql_escape_string()" translates in MySQL as "_mysql.escape_string()." The issue with this strategy is that without proper escaping for MySQL in Python, there can be problems associated with complicated strings, as they may not escape properly.

Inserting Variables

Another problem occurs when coders insert variables in MySQL tables. In the following snippet, the coder inserts four variables in a table.

cursor.execute ( “ " " INSERT INTO cupboard (shoes, socks, legwarmer, upper)
VALUES (nike, puma, wool, adidas) " " " )

This snippet might not work unless the coder adds a proper escape character such as “%s”. The double quotes used are also escape characters, but for escaping variables, the coder must use “%s”.

Percent Sign

The modifiers used in the SQL statement that are responsible for binding the parameters can be a little confusing. "Printf" style syntax is commonly used in the MySQL client library. The revised snippet should then look like this:

cursor.execute ( " " " INSERT INTO cupboard (shoes, socks, legwarmer, upper)
VALUES (%(nike)s,%( puma)s,%( wool)s,%(adidas)s) " " " )

However, adding the escape characters is not enough to alleviate the issue. Just adding these characters alone now makes the code susceptible to format string issues and open to malicious attacks.

Considerations

The coder may be able to code a safe, working code by adding further edits to the code. Here’s the final working snippet:

cursor.execute ( " " " INSERT INTO Songs cupboard (shoes, socks, legwarmer, upper)
VALUES (%s, %s, %s, %s )" " ", (nike, puma, wool, adidas))

Another problem can occur with the percentage symbol. If the coder uses a percentage sign in the script, (specifically in the query string, "failing to execute ()"), it should be properly escaped -- two percentage signs must be used. One of the percentage signs will act as the identifier for escape.

References

• Python.org: 11.13. sqlite3 — DB-API 2.0 Interface for SQLite Databases
• Tutorials Point:: Python – Strings with Built-in Methods
• Python Cookbook; Alex Martelli, et al.