C++ And SQLite - How to execute a query formed by user input?

Question

I'm working on a project where the user can insert a record into a SQLite database. The query will come generated automatically in the following method:

string ID = "";
string title = "";
string password = "";

cout << "Insert ID:\n";
cin >> ID;
cout << "Insert title of password:\n";
cin >> title;
cout << "Insert password:\n";
cin >> password;

string sql = "INSERT INTO test (ID,title,password) VALUES(" + ID + "," + title + "," + password + ");";

When i try to compile the program i get the error:

    classes.h:74:76: error: invalid operands of types ‘const char*’ and ‘const char [2]’ to binary ‘operator+’
   string sql = "INSERT INTO passwords (ID,title,password) VALUES (" + id + "," + title + "," + password 
                                                                            ^
classes.h:78:42: error: invalid operands of types ‘int’ and ‘sqlite3_stmt*’ to binary ‘operator&’
    sqlite3_prepare( db, sql.c_str(), -1 &st, NULL);

seem that he can't accept multiple character. Can someone show me how to fix this error?

P.S. I'm new in c++

Any help is appreciated. Thanks.

EDIT:

FULL CODE

sqlite3 *db;
sqlite3_stmt * st;
int id = 0;
string title = "";
string password = "";

cout << "Insert ID:\n";
        cin >> id;
        cout << "Insert title of password:\n";
        cin >> title;
        cout << "Insert password:\n";
        cin >> password;

        string sql = "INSERT INTO passwords (ID,title,password) VALUES (" + id + ',' + title + ',' + password + ");";

        if(sqlite3_open("pw.db", &db) == SQLITE_OK)
        {
            sqlite3_prepare( db, sql.c_str(), -1 &st, NULL);
            sqlite3_step( st );
        }
        else
        {
            cout << "Failed to connect\n";
        }
sqlite3_finalize(st);
sqlite3_close(db);

Never, ever, use raw user input to form a sql query, unless your purpose is to show why it is a bad idea. I don't care if it is some toy project. — Yakk - Adam Nevraumont, Apr 23 '16 at 19:12
`std::to_string(id)` (assuming the last smatter of code in this post is the *actual* problem, where `id` is indeed declared as `int`). — WhozCraig, Apr 23 '16 at 19:15
You should avoid directly putting user input into the SQL statement like this, that's how SQL injections happen. Consider using sqlite3_bind_*... See http://www.adp-gmbh.ch/sqlite/bind_insert.html — kfsone, Apr 23 '16 at 19:41

kfsone · Accepted Answer · 2016-04-24T18:12:45.307

You should avoid directly inserting user input into your SQL commands like this, a user could enter malicious text that deliberately alters the resulting SQL statement.

Instead, consider using parameter binding, this will allow you to avoid the string concatenation you're attempting to do. Your code:

    string sql = "INSERT INTO passwords (ID,title,password) VALUES (" + id + ',' + title + ',' + password + ");";

    if(sqlite3_open("pw.db", &db) == SQLITE_OK)
    {
        sqlite3_prepare( db, sql.c_str(), -1 &st, NULL);
        sqlite3_step( st );
    }

becomes

    string sql = "INSERT INTO passwords (ID,title,password) VALUES (?,?,?)";

    if(sqlite3_open("pw.db", &db) == SQLITE_OK)
    {
        sqlite3_prepare( db, sql.c_str(), -1 &st, NULL);
        sqlite3_bind_int(st, 1, ID);
        sqlite3_bind_text(st, 2, title.c_str(), title.length(), SQLITE_TRANSIENT);
        sqlite3_bind_text(st, 3, password.c_str(), password.length(), SQLITE_TRANSIENT);
        sqlite3_step( st );
    }

the 1, 2 and 3 are 1-based parameter indexes. See https://www.sqlite.org/c3ref/bind_blob.html

score -1 · Answer 2 · answered Apr 23 '16 at 19:06

-1

Your error message shows that the ID is declared as int in the database, but it gets a string from your c++ code. Change this line: string ID = ""; to this: int ID;

answered Apr 23 '16 at 19:06

Chris Tsiakoulas

186
5
12

The id is declared as a integer...anyway I added the full code – Apr 23 '16 at 19:11
Also check the quotes in the statement. string sql = "INSERT INTO passwords (ID,title,password) VALUES ('" + id + "','" + title + "','" + password + "');"; – Chris Tsiakoulas Apr 23 '16 at 19:26
Just found something useful http://stackoverflow.com/questions/11945980/sqlite-insert-statement-with-c – Chris Tsiakoulas Apr 23 '16 at 19:27

C++ And SQLite - How to execute a query formed by user input?

2 Answers2

Linked