Quoted keywords cannot be used as column values
qs5779 opened this issue · 2 comments
Let me first show the output for one of your examples.
$ sqlitis "SELECT id FROM foo WHERE id = 128"
select([foo.c.id]).where(text('id') == 128)
That looks good to me, so what is the issue with this?
$ sqlitis "SELECT ckey, cval FROM tinfo WHERE ckey = version"
select([tinfo.c.ckey, tinfo.c.cval]).where(text('ckey'))
There seems to be no comparison.
okay, apparently it doesn't like the fact that the ckey column could contain the word version, which in my very humble opinion still means it is broken.
$ sqlitis "SELECT ckey, cval FROM tinfo WHERE ckey = dbvers"
select([tinfo.c.ckey, tinfo.c.cval]).where(text('ckey') == text('dbvers'))
Hi @qs5779,
That looks good to me, so what is the issue with this?
$ sqlitis "SELECT ckey, cval FROM tinfo WHERE ckey = version"
select([tinfo.c.ckey, tinfo.c.cval]).where(text('ckey'))
There are two issues here:
-
Your SQL query is invalid. Since
version
is a string (text or varchar), it must be quoted:SELECT ckey, cval FROM tinfo WHERE ckey = version
(invalid)SELECT ckey, cval FROM tinfo WHERE ckey = 'version'
(valid)
-
There is still a bug.
version
is a keyword in some SQL dialects (at least sqlparse thinks so). Even with quoting sqlitis doesn't recognize'version'
as a string.
A workaround is to use either double-quotes ("version"
) or backticks (`version`
). However these are not typically valid SQL queries (because double-quotes and backticks are for reserved words / identifiers)
$ sqlitis 'SELECT ckey, cval FROM tinfo WHERE ckey = "version"'
select([tinfo.c.ckey, tinfo.c.cval]).where(text('ckey') == "version")
$ sqlitis 'SELECT ckey, cval FROM tinfo WHERE ckey = `version`'
select([tinfo.c.ckey, tinfo.c.cval]).where(text('ckey') == text('version'))