Sqlite database returns nonexistant column name instead of exception due to bad query-CodePudding

Found the issue:

SqlKata compiler was transforming the column names into string literals, so that was returned when a matching column was not located.
Updating the queries to use brackets instead of quotes resolved the issue.
Created github issue here regarding the issue: https://github.com/sqlkata/querybuilder/issues/655

Initial post contents retained below.

I was doing some unit testing against a Sqlite database, ensuring that my methods for creation and reading all work fine (They do). But One of the tests failed, and I am absolutely confused as to why.

The Sqlite db consists of a single table, defined below: TableName: Students Columns: ID (Primary Key), FirstName (string), LastName (string)

The following query works properly, returning the 'FirstName' value within the db:

"SELECT \"FirstName\" FROM \"Students\" WHERE \"ID\" = @p0"

The following query I would expect would cause an exception, since the column name does not exist:

"SELECT \"UnknownCol\" FROM \"Students\" WHERE \"ID\" = @p0"

Instead, I receive the value 'UnknownCol' as a string result.

For reference, I’m using the same method (which processes a DbCommand object) to perform the same thing at against an Excel file via OledbCommand. That function produces an exception (not a helpful one, but atleast it error our). So I know the underlying method works.

Why would sqlite return the name of a column that doesn't exist in that query?

Additional Info Edit:

Using an OledbConnection to read from an Excel sheet using the same method results in the following exception when I request an invalid column within the query (which while it doesn't tell you its a bad query due to invalid column name, atleast it errors out):

Exception Message: No value given for one or more required parameters.

Full code chain:

//db object has a method that returns a SqliteConnection, and has a 'Compiler' property that returns the SqlKata.Compiler object for SqlLite

var qry = new SqlKata.Query("Students").Select("UnknownCol").Where("ID",1);
return GetValue(db.GetConnection(), qry, db.Compiler);

//Results in the following sql: 
"SELECT \"UnknownCol\" FROM \"Students\" WHERE \"ID\" = 1"


---
public static object GetValue(DbConnection connection, Query query, SqlKata.Compilers.Compiler compiler)
{
    using (var cmd = connection.CreateCommand(query, compiler))
    {
        connection.Open();
        try
        {
            return cmd.ExecuteScalar();    
        }
        finally
        {
            connection.Close();
        }
    }
}

public static DbCommand CreateCommand(this DbConnection connection, SqlKata.Query query, SqlKata.Compilers.Compiler compiler)
{
    if (connection is null) throw new ArgumentNullException(nameof(connection));
    if (compiler is null) throw new ArgumentNullException(nameof(compiler));
    var result = compiler.Compile(query ?? throw new ArgumentNullException(nameof(query)));
    var cmd = connection.CreateCommand();
    cmd.CommandText = result.Sql;
    foreach (var p in result.NamedBindings)
    {
        _ = cmd.AddParameter(p.Key, p.Value);
    }
    return cmd;
}

public static DbParameter AddParameter(this DbCommand command, string name, object value)
{
    var par = command.CreateParameter();
    par.ParameterName = name;
    par.Value = value;
    command.Parameters.Add(par);
    return par;
}

CodePudding user response：

It's legal to select a string litteral in SQL. This is a valid SQL query which returns the mentioned string:

SELECT 'UnknownCol';

It will return a single row containing this string litteral. The following query is similar

SELECT 'UnknownCol' FROM students;

For each row in your table, it will return a row with this string litteral. Here is an example on a test table with a few rows in a test database:

sqlite> select 'a string litteral' from test;
a string litteral
a string litteral
a string litteral
a string litteral
a string litteral
sqlite> select count(1) from test;
5
sqlite>

If you want to query a specific column name instead of a string litteral you have to remove the '' characters around the column name. Then this is the result with an undefined column:

sqlite> select unknowncol from test;
Parse error: no such column: unknowncol
  select unknowncol from test;
         ^--- error here
sqlite>

or for a defined column:

sqlite> select id from test;
1
2
3
4
6
sqlite>