C++ user input to sqlite3

Question

I have a class assignment that asks us to write a C++ program that tracks spending, allows modification of the records, and returns "satisfaction" numbers about individual expenses (i.e. how good the user felt about spending that money). Our instructor has indicated that he'd like us to use sqlite3 in this program. He's given us a sample program that builds a table in sqlite3 and inputs predetermined values for the columns. This program runs just fine without issue.

What I am trying to do is modify the program to accept user inputs and store them in the sqlite3 database. This is the code I have thus far:

int main()
{
    string salesDesc;
    int price;
    int satisf;
    sqlite3 *db;
    char *szErrMsg = 0;

    cout << "Description of Expense: ";
    cin >> saleDesc;
    cout << endl;

    cout << "Price: ";
    cin >> price;
    cout << endl;

    cout << "Your Satisfaction: ";
    cin >> satisf;
    cout << endl;

    // open database
    int rc = sqlite3_open("spending_track.sqlite", &db);
    if (rc)
    {
        cout << "Cannot open database\n";
    }
    else
    {
        cout << "Database opened successfully\n";
    }

    const char *pSQL[6];
    pSQL[0] = "CREATE TABLE IF NOT EXISTS expenses(id INTEGER PRIMARY KEY "
              "AUTOINCREMENT NOT NULL, logged TIMESTAMP DEFAULT "
              "CURRENT_TIMESTAMP NOT NULL, desc VARCHAR(40), price INT,"
              "satisfaction INT)";
    pSQL[1] = "INSERT INTO expenses('" + string(saleDesc) + "'," price "," satisf ")";
    pSQL[2] = "SELECT * FROM expenses";
    pSQL[3] = "SELECT sum(satisf) FROM expenses";

    // blablabla the rest of the program

When I try to compile this, I receive the following error:

error: cannot convert 'std::_cxx11::basic_string' to 'const char*' in assignment pSQL[1] = "INSERT INTO expenses('" + string(saleDesc) + "'," price "," satisf ")";

If I change string(saleDesc) to saleDesc, I get the same error.

If I change string saleDesc; to char* saleDesc;, I receive the following error:

error: invalid operands of types 'const char[23]' and 'char*' to binary 'operator+' pSQL[1] = "INSERT INTO expenses('" + string(saleDesc) + "'," price "," satisf ")";

I'm not sure what else to try to get this to work. I have also heard that it's a bad idea to allow users to directly input to sqlite3 tables. What would be a more "proper" way to do this?

You can't use char* like that. It's time to ditch char* and use std::string instead, or even std::stringstream (to allow easier creation of SQL statements) — Tas
– Tas, Commented Apr 4, 2018 at 22:36
It is a bad idea to take user input and put it straight into a SQL statement, but for your assignment it shouldn't matter. You should filter their input though. Relevant XKCD — Tas
– Tas, Commented Apr 4, 2018 at 22:37
If you want to avoid injection issues, check out prepared statements — Jack
– Jack, Commented Apr 4, 2018 at 22:45
String doesn't work, either. See above. How do I "filter" an input? This is my first programming class so I'm fairly new to C++, and we only just looked at SQL for the first time last week. — KingKenny04
– KingKenny04, Commented Apr 4, 2018 at 22:55

dempzorz · Accepted Answer · 2018-04-04 23:24:02Z

Since this is just a class assignment, I doubt that you are going to have to worry about SQL injection attacks, so I wouldn't bother trying to sanitize your input.

Your other issue is you are confusing char*s and std::strings. The sqlite API requires you to pass it char*s so it can be used from C code, however that doesn't mean you need to use them. std::string is a wrapper for the char array, which you can get with the c_str() method. I don't think you really need to put the SQL statements in an array at the end. How about something like this:

std::string  addTable = "CREATE TABLE IF NOT EXISTS expenses(id INTEGER PRIMARY KEY "
                        "AUTOINCREMENT NOT NULL, logged TIMESTAMP DEFAULT "
                        "CURRENT_TIMESTAMP NOT NULL, desc VARCHAR(40), price INT,"
                        "satisfaction INT)";
std::string insertExpense = "INSERT INTO expenses('" + saleDesc + "'," + std::to_string(price) "," + std::to_string(satisf) + ")";
std::string selectAllExpenses = "SELECT * FROM expenses";

Then when you want to pass it to the sqlite API, you could use c_str()

sqlite3_exec(db, addTable.c_str(), ...

KingKenny04 · Accepted Answer · 2018-04-06 01:54:52Z

Thanks everyone for the responses. I spent about an hour and a half with my professor yesterday going over this, and this actually stumped him. I eventually found a way to make this work with the array, but I want to stress that the solution I came up with is pretty much only good for this assignment. For anyone reading this with a similar problem, this method is not only messy, but also allows for SQL injection which should be avoided.

The problem, as many here have mentioned in comments, was that I was trying to stick a string into a char* array. The workaround we came up with was to add the SQL commands with the variables expanded in them directly to a string variable, like so:

string insertExpense = "INSERT INTO expenses(desc, price, satisf) VALUES ('" + saleDesc + "', "
                        ""+ to_string(price) + ", " + to_string(satisf) + ")";

We then made that variable a c_str and assigned it to a char* variable, like so:

const char *line1 = insertExpense.c_str();

We then simply assigned this char* variable directly to the correct position in the array, like so:

const char *pSQL[6];
pSQL[0] = "CREATE TABLE IF NOT EXISTS expenses(id INTEGER PRIMARY KEY "
          "AUTOINCREMENT NOT NULL, logged TIMESTAMP DEFAULT "
          "CURRENT_TIMESTAMP NOT NULL, desc VARCHAR(40), price REAL,"
          "satisf INT)";
pSQL[1] = line1;
pSQL[2] = "SELECT * FROM expenses";
pSQL[3] = "SELECT sum(satisf) FROM expenses";

This method correctly makes the SQL table and populates it with the correct statements as stored in their respective variables. I want to stress again that this method is both very messy and dangerous, and for anyone with a similar issue, it is probably a much better idea to use prepared statements, as others in the comments have already mentioned. Thank you everyone!

Collectives™ on Stack Overflow

C++ user input to sqlite3

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related