Psycopg2 Equivalent of MySQLdb.Escape_String

A good way to escape quotes in a database query string?

If it's part of a Database query you should be able to use a Parameterized SQL Statement.

As well as escaping your quotes, this will deal with all special characters and will protect you from SQL injection attacks.

Escape input data for postgres

Just pass query parameters as a second argument to execute, like:

>>> cur.execute(
...     """INSERT INTO some_table (an_int, a_date, a_string)
...         VALUES (%s, %s, %s);""",
...     (10, datetime.date(2005, 11, 18), "O'Reilly"))

Then, all of the parameters will be properly escaped.

This is because psycopg2 follows Python Database API Specification v2.0 and supports safe parameterized queries.

Also see:

• Parameterized queries with psycopg2 / Python DB-API and PostgreSQL
• psycopg2 equivalent of mysqldb.escape_string?

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

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.

How to use like pattern matching with PostgreSQL and Python with multiple percentage (%) symbols?

Instead of embedding the ampersands in the query string, you could wrap the search term string in ampersands, and then pass that to cursor.execute():

sql = 'SELECT * from FishPond fp WHERE LOWER(fp.name) LIKE LOWER(%s)'
search_term = 'xyz'
like_pattern = '%{}%'.format(search_term)
cur.execute(sql, (like_pattern,))

The query is simplified for the purpose of example.

This is more flexible because the calling code can pass any valid LIKE pattern to the query.

BTW: In Postgresql you can use ILIKE for case insensitive pattern matching, so the example query could be written as this:

sql = 'SELECT * from FishPond fp WHERE fp.name ILIKE %s'

As noted in the documentation ILIKE is a Postgresql extension, not standard SQL.

---

Related Topics