Search code examples
pythonsqliteapostrophe

Python, SQLite: Apostrophe in '%s'

I'm getting a syntax error when trying to execute SQLite3 query. The problem is because variable inputPattern contains 's.

The function is:

def searchForExactEnglishToCzechTranslation(inputPattern,db):
    if "\'" in inputPattern:
        inputPattern.replace("'","''")
    result = db.execute("SELECT czech FROM translations WHERE english='%s'"  % (inputPattern)).fetchall()

I'm trying to replace the sign ' but still isn't working.

Will you give me an advice? Thanks

Solution

  • Don't use interpolation; use SQL parameters:

    def searchForExactEnglishToCzechTranslation(inputPattern,db):
    result = db.execute("SELECT czech FROM translations WHERE english=?",
                        (inputPattern,)).fetchall()

    This takes care of quoting (the ? is not surrounded by quotes) and escaping.

    Quoting the sqlite3 module documentation:

    Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see http://xkcd.com/327/ for humorous example of what can go wrong).

    Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.)