SqliteSyntaxException: near "0": syntax error [C# Unity]

Question

I'm trying to use SQLite integration with Unity3D, like in somes tutorials. But i'm having a problem..

This is my create table:

public void CreateTablePaciente () 
{
    string sqlCreatePacientes;
    DbConnection connection = new SqliteConnection(urlDataBase);
    IDbCommand command = connection.CreateCommand();
    connection.Open();
    sqlCreatePacientes = "CREATE TABLE IF NOT EXISTS " + tabela 
        + " (cpf TEXT PRIMARY KEY, "
        + "nome TEXT NOT NULL, "
        + "sexo TEXT NOT NULL, "
        + "endereco TEXT NOT NULL, " 
        + "email TEXT, "
        + "dataNascimento TEXT NOT NULL, "
        + "telefone TEXT NOT NULL, "
        + "numeroConsultas TEXT NOT NULL"
        + ");";

    command.CommandText = sqlCreatePacientes;
    command.ExecuteNonQuery ();
}

And this is my Insert method:

public void InsertPaciente (Paciente paciente) 
{
    string sqlInsert;
    DbConnection connection = new SqliteConnection(urlDataBase);
    IDbCommand command = connection.CreateCommand();

    sqlInsert = "INSERT INTO " + tabela 
        + " (cpf, nome, sexo, endereco, email, dataNascimento, telefone, numeroConsultas)"
        + " VALUES (" 
        + paciente.Cpf + ", "
        + paciente.Nome + ", "
        + paciente.Sexo + ", "
        + paciente.Endereco + ", "
        + paciente.Email + ", "
        + paciente.DataNascimento + ", "
        + paciente.Telefone + ", "
        + paciente.NumeroConsultas 
        + ");";

    connection.Open();
    command.CommandText = sqlInsert;
    Debug.Log (sqlInsert);
    command.ExecuteNonQuery();
}

Insert Query:

INSERT INTO paciente (cpf, nome, sexo, endereco, email, dataNascimento, telefone, numeroConsultas) VALUES (00000000000, Nome 0, m, rua aposkpaosk0, aosais@asiapos.xpco0, 0/00/00, 00000000000, 0);

But i'm having this SQLite error..

SqliteSyntaxException: near "0": syntax error

Mono.Data.SqliteClient.SqliteCommand.GetNextStatement (IntPtr pzStart, System.IntPtr& pzTail, System.IntPtr& pStmt)

Mono.Data.SqliteClient.SqliteCommand.ExecuteReader (CommandBehavior behavior, Boolean want_results, System.Int32& rows_affected)

Mono.Data.SqliteClient.SqliteCommand.ExecuteNonQuery ()

PacienteDAO.InsertPaciente (.Paciente paciente) (at Assets/C#Script/PacienteDAO.cs:73)

Seems to be alright with the create table, but i don't know what this "near: '0'" means.. How i can fix it?

Don't write SQL execution using string concatenation, use parameters. — Lasse V. Karlsen, Dec 12 '16 at 18:31

score 1 · Answer 1 · answered Dec 12 '16 at 20:19

SQL string literals need to be wrapped with single quotes.

All you'd have to do to figure out whats wrong is paste the query into SQL Management Studio and you'll get the red squigglies

INSERT INTO paciente (cpf, nome, sexo, endereco, email, dataNascimento, telefone, numeroConsultas)
VALUES ('00000000000', 'Nome 0', 'm', 'rua aposkpaosk0', 'aosais@asiapos.xpco0', '0/00/00', '00000000000', 0);

score 1 · Accepted Answer · edited May 23 '17 at 10:29

You better use bind parameters in your SQL Statement instead of trying to overcome all the possible SQL injection attacks that your code exposes by trying to use any concatenation of strings. I've changed your Insert method as follows to introduce the bind parameters in your statement and add the values from your class to the Parameters collection of the DBCommand instance.

Also notice that I added using statements for those objects that implement IDisposable. This should reduce the risk of memory leaks, which is explicitly mentioned in the System.Dats.SqlLite FAQ

public void InsertPaciente (Paciente paciente) 
{
    string sqlInsert;
    // dispose when done
    using(DbConnection connection = new SQLiteConnection(urlDataBase))
    {
        // dispose when done
        using(IDbCommand command = connection.CreateCommand())
        {
            // use named parameters 
            // https://www.sqlite.org/c3ref/bind_blob.html
            sqlInsert = @"INSERT INTO paciente 
                (cpf, nome, sexo, endereco, email, dataNascimento, telefone, numeroConsultas)
                VALUES 
                (@Cpf, @Nome, @Sexo, @Endereco, @Email, @DataNascimento, @Telefone, @NumeroConsultas 
                );";

            connection.Open();
            command.CommandText = sqlInsert;
            // for each parameter, add the name of the bind-parameter and the value 
            command.Parameters.Add(new SQLiteParameter("Cpf",paciente.Cpf));
            command.Parameters.Add(new SQLiteParameter("Nome", paciente.Nome));
            command.Parameters.Add(new SQLiteParameter("Sexo", paciente.Sexo));
            command.Parameters.Add(new SQLiteParameter("Endereco", paciente.Endereco));
            command.Parameters.Add(new SQLiteParameter("Email", paciente.Email));
            command.Parameters.Add(new SQLiteParameter("DataNascimento", paciente.DataNascimento));
            command.Parameters.Add(new SQLiteParameter("Telefone", paciente.Telefone));
            command.Parameters.Add(new SQLiteParameter("NumeroConsultas", paciente.NumeroConsultas));
            //Debug.Log (sqlInsert);
            command.ExecuteNonQuery();
        }
    }
}

SqliteSyntaxException: near "0": syntax error [C# Unity]

2 Answers2