SQLite Insert Statement with C++

Question

Below is the code I have tried on a test2.cpp

I checked my database and found out no new record had been added. What actually went wrong in my statement?

#include <iostream>
#include <sqlite3.h>

//g++ -o test2 test2.cpp -lsqlite3
using namespace std;

int main()
{
int counter = 0;

    sqlite3 *db;
    sqlite3_stmt * stmt;

string username = "panda";
string name = "Kungfu Panda";
string department = "normal";
string password = "hellopassword";

string sqlstatement = "INSERT INTO abe_account (" + username + "," + name + "," + department + "," + password + ");";

    if (sqlite3_open("abeserver.db", &db) == SQLITE_OK)
    {
    sqlite3_prepare( db, sqlstatement.c_str(), -1, &stmt, NULL );//preparing the statement
    sqlite3_step( stmt );//executing the statement
        }
    else
    {
        cout << "Failed to open db\n";
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);


    return 0;

}

I would like to ask if it's possible to know if the statement is executed with success too. Like one row added, some form of confirmation from sqlite3. And if there's an error, will it be able to cout out too?

[You can Create database,table and insert data into table,][1] [1]: http://stackoverflow.com/questions/10540728/creating-an-sqlite3-database-file-through-objective-c/11795399#11795399 Hope it will help you. — Vineesh TP, Aug 14 '12 at 05:33

score 4 · Accepted Answer · answered Aug 14 '12 at 05:31

When you do an insert, you generally specify the fields in the order that you are going to supply the data. Otherwise you have to specify all data in the correct order (passing a value for all fields in the table in the order it was defined).

Your syntax is therefore incomplete... Either do this:

INSERT INTO tablename (field1, field2, field3) VALUES (value1, value2, value3);

Or this:

INSERT INTO tablename VALUES (value1, value2, value3)

Also, because you are not binding data to a prepared query, you need to quote your strings. It's not okay to just substitute in panda for the username. You need to supply 'panda'. Therefore, the strings go in like this:

"('" + username + "','" + name + "','" + department + "','" + password + "');"

Because it's easy to mess this up (and there are escape codes for special characters, eg the single-quote), you might prefer to make a function to quote the string which at the very least would do:

string quotesql( const string& s ) {
    return string("'") + s + string("'");
}

Then:

"(" + quotesql(username) + "," + quotesql(name) + ...

So, all up you might end up with this (assuming field names):

string sqlstatement =
    "INSERT INTO abe_account (username, name, department, password) VALUES ("
    + quotesql(username) + ","
    + quotesql(name) + ","
    + quotesql(department) + ","
    + quotesql(password) + ");";

Also don't do this, unless the data you are inserting isn't user driven. This is a classic way to introduce a SQL Injection attack. You either need to escape the values or use parameters. — David Bradley, May 04 '22 at 01:24

score 1 · Answer 2 · answered Aug 14 '12 at 05:18

Try adding some single quotes around your strings:

string sqlstatement = "INSERT INTO abe_account ('" + username + "','" + name + "','" + department + "','" + password + ");";

Not familiar with sqllite, but usually an INSERT looks like:

string sqlstatement = "INSERT INTO abe_account (ColumnName1, ColumnName2, ColumnName3, ColumnName4) VALUES ('" + username + "','" + name + "','" + department + "','" + password + "');";

score 0 · Answer 3 · answered Aug 14 '12 at 05:41

Yes you can know for why its failing.

include "sqlite_exception.h"

follow this way:

int m = sqlite3_step(stmt);
if(m == SQLITE_BUSY)
{

/*try doing sqlite3_step again, with a delay using sleep() **/ }

if(m == SQLITE_ERROR)
    std::cout(sqlite3_sql(stmt), sqlite3_errmsg(db));
if(m == SQLITE_MISUSE)
    std::cout(sqlite3_sql(stmt), sqlite3_errmsg(db));

have a look at these: http://www.sqlite.org/c3ref/c_abort.html

SQLite Insert Statement with C++

3 Answers3

include "sqlite_exception.h"

Linked