Why does sqlite3 not escape metacharacters with parameterized values?

I’m making a database interface in python for a project where i need to encrypt everything that goes into my database and decrypt everything that goes out of it.

Unfortunately some of my characters get encrypted to meta characters in sql and this gives trouble when selecting something from the database. The first thing that came up was to use prepared statements in sqlite with the ‘?’ character and parameterized values like the following:

con = sqlite3.connect('database.db')
cursorObj = self.con.cursor()

sqlStatement = "SELECT * FROM usertable WHERE username LIKE '%' || ? || '%'"
self.cursorObj.execute(sqlStatement, ("_", ))

rows = self.cursorObj.fetchall()

The thing is that with this statement i select all rows from my database, not all rows that contain the ‘_’ . The same thing with the ‘%’ character. I’m aware that those characters are like a regex for SQL where with the ‘_’ you select any one character and with ‘%’ you select any one or more characters. I thought that with parameterized values you would avoid these problems and you can freely put any character in the tuple for parameterized values, wildcard characters included, because sqlite should take care of escaping them anyway.

My goal is to just get back the values with literally ‘_’ and ‘%’ in them without getting any unwanted rows from the database in the process. I would like to avoid any metacharacter checking outside of the above mentioned logic (i.e. in a special ‘metacharacter check function’)

Hope your answers can clarify this for me because I’ve been trying to figure it out for a while now but I get more stuck the more I look at it. thanks in advance :-).

— EDIT

after testing this some more it seems this problem only shows up when using LIKE ? and not with WHERE column = ?. With the WHERE it actually escapes it properly.

Answer

You have to escape metacharacters in LIKE search strings by prefixing them with a user-specified character. For example:

username LIKE '%' || ? || '%' ESCAPE '#'

and then make sure every underscore and % in the string you’re binding to that parameter has a # before it.

In python, you can do that part with the str.translate() method to map the relevant Unicode codepoints to strings (Including escaping the escape character in case it’s present):

>>> teststr='foo%bar_baz%'
>>> teststr.translate({ord('%'):'#%', ord('_'):'#_', ord('#'):'##'})
'foo#%bar#_baz#%'