SQLite error unrecognized token Unity

Question

I am using Unity, here is a little snippet of my code. All I want to do is to put the data.token and the data.expire into SQLite. For some reason it keeps throwing me an error that is:

SqliteException: SQLite error
unrecognized token: "587503bc773a565d52401c87"
Mono.Data.Sqlite.SQLite3.Prepare (Mono.Data.Sqlite.SqliteConnection cnn, System.String strSql, Mono.Data.Sqlite.SqliteStatement previous, UInt32  timeoutMS, System.String& strRemain)
Mono.Data.Sqlite.SqliteCommand.BuildNextCommand ()

I have no idea how the token is unrecognized, In SQLite the Token field is a STRING and the Expire field is an INTEGER.

IncomingTokenData data = IncomingTokenData.CreateFromJSON(www.text);

string conn = "URI=file:" + Application.dataPath + "/MyDataBase.s3db"; //Path to database.
var sqlQuery = "INSERT INTO MyDataBase(Token, Expire) VALUES(" + data.token +", " + data.expire + ")";

if(!string.IsNullOrEmpty(www.error)) {
    ErrText.text = "Error: " + www.error;
}else{
    if(data.pass == "1"){
        IDbConnection dbconn;
        dbconn = (IDbConnection) new SqliteConnection(conn);
        dbconn.Open(); //Open connection to the database.
        IDbCommand dbcmd = dbconn.CreateCommand();
        dbcmd.CommandText = sqlQuery;
        dbcmd.ExecuteNonQuery();

Serlite · Accepted Answer · 2017-01-10T16:53:41.037

2

In SQL queries, you should enclose string values in single quotes (in this case, place quotes around data.token):

var sqlQuery = "INSERT INTO MyDataBase(Token, Expire) VALUES('" + data.token +"', " + data.expire + ")";

Note that string concatenation isn't the best way to build up SQL queries - a more robust way to avoid these problems is to use placeholders, like the built-in functionality IDbCommand has for adding parameters:

var sqlQuery = "INSERT INTO MyDataBase(Token, Expire) VALUES(@token, @expire)";

if(!string.IsNullOrEmpty(www.error)) {
    ErrText.text = "Error: " + www.error;
}else{
    if(data.pass == "1"){
        IDbConnection dbconn;
        dbconn = (IDbConnection) new SqliteConnection(conn);
        dbconn.Open(); //Open connection to the database.
        IDbCommand dbcmd = dbconn.CreateCommand();
        dbcmd.Parameters.Add("@token", SqlDbType.VarChar).Value = data.token;
        dbcmd.Parameters.Add("@expire", SqlDbType.Int).Value = data.expire;
        dbcmd.CommandText = sqlQuery;
        dbcmd.ExecuteNonQuery();

Using this method, the values are properly formatted according to their data type.

edited Jan 10 '17 at 16:53

answered Jan 10 '17 at 16:19

Serlite

12,130
5
38
49

@CorySparks Glad to help out! Sometimes it's easier to spot these things if you build up the query using `String.Format()`, but do what's best for you. – Serlite Jan 10 '17 at 16:27
Does the `IDbConnection` thingy not have a way to use placeholders? – simbabque Jan 10 '17 at 16:31
@simbabque Ah, you're absolutely right. I forgot that SQLite actually has its own way to add parameters, as described in the highest-voted answer of [Adding parameters in SQLite with C#](http://stackoverflow.com/questions/809246/adding-parameters-in-sqlite-with-c-sharp) - this is the way I preferred when I dabbled in SQLite. – Serlite Jan 10 '17 at 16:33
Actually that's mostly the driver who's doing it. Some RDBMS support the `?` placeholders themselves, but not all, so the driver emulates them by doing proper quoting. In any case, you should update your answer to say what's wrong, but to also point out that one should **always** use the placeholders. SQL injection can happen to non-web applications too, and we don't want mom deleting our savegames, do we? :) – simbabque Jan 10 '17 at 16:37
1

@simbabque Thanks for your insights! I've added the recommendation and modified code to the answer. – Serlite Jan 10 '17 at 16:55
Named placeholders are fun. Now I also learned something. I came here because I was browsing unity3d questions and figured "hey, sqlite sounds like I can answer it" :D – simbabque Jan 10 '17 at 16:57

SQLite error unrecognized token Unity

1 Answers1