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]: stackoverflow.com/questions/10540728/… Hope it will help you. — Vineesh TP
– Vineesh TP, Commented Aug 14, 2012 at 5:33

paddy · Accepted Answer · 2012-08-14 05:31:26Z

4

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) + ");";

answered Aug 14, 2012 at 5:31

paddy

63.7k7 gold badges68 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Bradley Over a year ago

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.

criticalfix · Accepted Answer · 2012-08-14 05:18:50Z

1

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 + "');";

answered Aug 14, 2012 at 5:18

criticalfix

2,8701 gold badge21 silver badges32 bronze badges

Comments

Sks Sapare · Accepted Answer · 2012-08-14 05:41:51Z

0

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

answered Aug 14, 2012 at 5:41

Sks Sapare

371 bronze badge

Collectives™ on Stack Overflow

SQLite Insert Statement with C++

3 Answers 3

1 Comment

Comments

include "sqlite_exception.h"

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

include "sqlite_exception.h"

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related