SQLite treating hyphens as arithmetic operators

Question

In a React Native App I'm attempting to insert data into a local sqlite db

    let submissionID = "1-2-3";
    this.dbQuery("INSERT INTO Submissions (ID, Data) VALUES("+submissionID+",'Test')");

(dbQuery is the name of a function I made to simplify my queries but the statement inside it should be the same)

If I viewed the Submissions table after this insert statement I would expect to see a row with [ID:"1-2-3",Data:"Test"] but instead I see [ID:"-4",Data:"Test"]

I created the table like so

CREATE TABLE IF NOT EXISTS Submissions(ID BLOB PRIMARY KEY NOT NULL, Data BLOB NOT NULL)

I used Blob because I read "The value is a blob of data, stored exactly as it was input." but I've also tried Text. I've also casted submissionID as a string like so

this.dbQuery("INSERT INTO Submissions (ID, Data) VALUES("+String(submissionID)+",'Test')");

But none of that worked. I do see here how sqlite takes advantage of arithmetic operators https://www.w3resource.com/sqlite/arithmetic-operators.php but I'm not sure how to stop it from doing so.

How would I get sqlite to treat my hyphens as hyphens instead of subtraction signs?

score 2 · Accepted Answer · answered Apr 29 '19 at 20:22

What you're doing is the equivalent of:

this.dbQuery("INSERT INTO Submissions (ID, Data) VALUES(1-2-3,'Test')");

passing the numeric expression 1-2-3 to the INSERT statement. The simplest fix is to quote the string literal.

let submissionID = "1-2-3";
this.dbQuery("INSERT INTO Submissions (ID, Data) VALUES('"+submissionID+"','Test')");

However, to guard against SQL injection attacks, you really ought to be using prepared statements instead of using string concatenation to build SQL statements.

Thanks that worked! The user has no control over submissionID since it isn't based on any actual input from them. Are using prepared statements still necessary in that case? — Matt, Apr 29 '19 at 20:30

MikeT · Answer 2 · 2019-04-29T20:30:14.653

Enclose the string in single quotes i.e.

this.dbQuery("INSERT INTO Submissions (ID, Data) VALUES('"+String(submissionID)+"','Test')");

Thus the value is treated as a literal by SQLite, without enclosing the value it will either be treated as a numeric value or as an identifier (column, table, trigger, view depending upon where it is coded and thus what the parser expects).

The data type (column affinity) has little bearing other than if you specified ID INTEGER PRIMARY KEY, then you could not store anything other than an integer. As ID INTEGER PRIMARY key has a special interpretation that is the column is an alias of the rowid.

I used Blob because I read "The value is a blob of data, stored exactly as it was input." but I've also tried Text. I've also casted submissionID as a string like so

That is only if the value to be inserted is a BLOB byte[] or in the case of raw SQL x'FF01FE02', otherwise SQLite will store the value according to how it interprets the type should be stored.

SQLite treating hyphens as arithmetic operators

2 Answers2