SQLite select with string equality where value is same as column name?

Question

I have an SQLite (v3.8.7.1 2014-10-29) database with a table containing names of locally installed software packages including details of version, date installed, software home page URL, etc.

Now the problem has arisen that one of the packages (CPAN module "version") happens to have the same name as one of the table columns, viz "version".

In the most simple test case for a table "Packages"

name	version
delete	2.2.5
replace	1.2.5
search	3.1
version	0.2.3

echo 'SELECT * FROM Packages where name = "replace" ;' | sqlite3 Test.db
replace|1.2.5

echo 'SELECT * FROM Packages WHERE name = "search" ;' | sqlite3 Test.db
search|3.1

But when trying to retrieve the entry for the package named "version",

echo 'SELECT * FROM Packages WHERE name = "version" ;' | sqlite3 Test.db

nothing is returned, because presumably it is trying to compare the name with the values in the "version" column.

Is there a way to force a literal string comparison or is the only solution to avoid such problems is to rename the column from "version" to something else, e.g. "package_version", which is hopefully never going to collide with a possible package name string?

score 4 · Answer 1 · edited Mar 23 '21 at 21:51

This is the same issue as in

sqlite SELECT returns all records when querying a column for a value with the same name as the column

which I missed when doing a search for possible answers.

The solution from Sky Sanders is to use single quotes around the string value.

echo "SELECT * FROM Packages WHERE name = 'version' ;" | sqlite3 Test.db

Explicit single quotes prevent the expansion of shell variables, so that problem can be overcome by using a shell variable which expands to a single quote, viz

name="version"
quote="'"
table="Packages"

echo "SELECT * FROM ${table} WHERE name = ${quote}${name}${quote} ;" | sqlite3 Test.db

SQLite select with string equality where value is same as column name?

1 Answers1