"ambiguous column name" in SQLite INNER JOIN

Question

I have two tables in a SQLite DB, INVITEM and SHOPITEM. Their shared attribute is ItemId and I want to perform an INNER JOIN. Here's the query:

    SELECT  INVITEM.CharId AS CharId, 
            INVITEM.ItemId AS ItemId 
      FROM  (INVITEM as INVITEM 
INNER JOIN  SHOPITEM AS SHOPITEM 
        ON  SHOPITEM.ItemId = INVITEM.ItemId)
     WHERE  ItemId = 3;

SQLite doesn't like it :

SQL error: ambiguous column name: ItemId

The error goes away if I write WHERE INVITEM.ItemId = 3, but since the WHERE condition is more or less user-specified, I rather make it work without having to specify the table. NATURAL JOIN seems to solve the issue, but I'm not sure if the solution is general enough (ie I could use in this case, but I'm not sure if I can use in every case)

Any alternate SQL syntax that would fix the problem?

Answer 1

I would write this query this way:

SELECT i.CharId AS CharId, i.ItemId AS ItemId 
FROM INVITEM as i INNER JOIN SHOPITEM AS s USING (ItemId)
WHERE i.ItemId = 3;

I'm using the USING (ItemId) syntax which is just a matter of taste. It's equivalent to ON (i.ItemID = s.ItemID).

But I resolved the ambiguity by qualifying i.ItemID in the WHERE clause. You would think this is unnecessary, since i.ItemID = s.ItemID. They're both equal by commutativity, so there's no semantic ambiguity. But apparently SQLite isn't smart enough to know that.

I don't like to use NATURAL JOIN. It's equivalent to an equi-join of every column that exists in both tables with the same name. I don't like to use this because I don't want it to compare columns that I don't want it to, simply because they have the same name.

Answer 2

I would steer clear of allowing the user to write SQL clauses directly. This is the source of SQL Injection vulnerabilities.

If you need the query to be flexible, try parsing the user's input and adding the appropriate where clause.

Here is some C# code to show the general idea

// from user input
string user_column = "ItemID";
string user_value = "3";

string sql = "SELECT INVITEM.CharId AS CharId, INVITEM.ItemId AS ItemId FROM (INVITEM as INVITEM INNER JOIN SHOPITEM AS SHOPITEM ON SHOPITEM.ItemId = INVITEM.ItemId) ";

if (user_column == "ItemID")
{
    // using Int32.Parse here to prevent rubbish like "0 OR 1=1; --" being entered.
    sql += string.Format("WHERE INVITEM.ItemID={0}",Int32.Parse(user_value));
}

Obviously if you're dealing with more than one clause, you'd have to substitute AND for WHERE in subsequent clauses.