syntax error with insert statement

Question

I am using c/c++ with sqlite. My code shown below compiles but after I entered my password, I encountered this error.

    SQL error: near "NSERT": syntax error

I am unable to identify where exactly the syntax error is.

code

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 
#include <string>
#include <iostream>


void insertIntoTable(std::string userName,std::string password);

int main () {
    std::string userName;
    std::string password;
    std::cout << "Enter a user name" << std::endl;
    std::cin >> userName;
    std::cout << "Enter a password" << std::endl;
    std::cin >> password
    insertIntoTable(userName,password);
} 

//method to insert into table
void insertIntoTable(std::string userName,std::string password) {
    sqlite3 *db;
    char *zErrMsg = 0;
    int  rc;
    const char *sql;
    int i = 1;
    int j = 1;

    rc = sqlite3_open("test.db", &db);
    if( rc )
    {
       fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
       exit(0);
    }else
    {
       fprintf(stderr, "Opened database successfully\n");
    }
   //query
   sql = "INSERT INTO Login (_id,username,password,manager_id) "  \
         "VALUES ("+i,userName,password,j+");"; 
}

Please help. thanks

In the expression, sql = "INSERT INTO Login (_id,username,password,manager_id) " \ "VALUES ("+i,userName,password,j+");";, the comma-operator makes it like sql = "INSERT INTO Login (_id,username,password,manager_id) " \ "VALUES ("+i; /* plus other junk that is effectively ignored (as the expressions are evaluated but discarded) */ — user2864740
– user2864740, Commented Jan 21, 2014 at 14:35

Mike Seymour · Accepted Answer · 2014-01-21 14:47:54Z

sql = "INSERT INTO Login (_id,username,password,manager_id) "  \
      "VALUES ("+i,userName,password,j+");";

That is nonsense. You can't build strings by adding non-string values to a C-style character array or pointer; and the comma operator doesn't append a comma to the string, but instead discards the value of the previous expression after performing its side effects. This means that the whole expression is equivalent to

sql = "INSERT..." + i;

which adds the value of i (1) to the address of a string literal to get a pointer to the second character; so the overall result is NSERT INTO Login (_id,username,password,manager_id) VALUES (

You want to use std::string. In C++11, there are handy functions to convert numbers to strings:

std::string sql = "INSERT INTO Login (_id,username,password,manager_id) "
                  "VALUES (" + std::to_string(i) + ',' 
                             + userName + ','
                             + password + ',' 
                             + std::to_string(j) + ");";

Historically, you would need a string-stream

std::stringstream s;
s << "INSERT INTO Login (_id,username,password,manager_id) VALUES ("
  << i << ',' << userName << ',' << password << ',' << j << ");";
std::string sql = s.str();

Sebastian Redl · Accepted Answer · 2014-01-21 14:55:28Z

2

All of these answers are terribly wrong. String interpolation for SQL query execution is the express train into injection land. There is no reason why you should ever use it.

The correct way is to use sqlite3_bind to bind values against placeholders in the prepared statement, i.e. the code should look like this:

sql = "INSERT INTO Login (_id,username,password,manager_id) "
      "VALUES (?, ?, ?, ?);";
sqlite3_stmt* stmt;
rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, nullptr);
// handle rc
// Now bind the parameters.
sqlite3_bind_int(stmt, 1, i);
sqlite3_bind_text(stmt, 2, userName.c_str(), userName.size(), SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 3, password.c_str(), password.size(), SQLITE_TRANSIENT);
sqlite3_bind_int(stmt, 4, j);
// Now execute.

answered Jan 21, 2014 at 14:55

Sebastian Redl

73.9k8 gold badges137 silver badges177 bronze badges

1 Comment

user3213758 Over a year ago

i will try it out.Now

Alan Doyle · Accepted Answer · 2014-01-21 14:30:04Z

1

I'm 80% sure this is how its supposed to be... not sure because its not pure SQL but.. yep

sql = "INSERT INTO `Login` (`_id`, `username`, `password`, `manager_id`) "  \
     "VALUES ("+i+", '"+userName+"', '"+password+"', "+j+");";

answered Jan 21, 2014 at 14:30

Alan Doyle

10816 bronze badges

1 Comment

user3213758 Over a year ago

error: invalid operands of types ‘const char*’ and ‘const char [4]’ to binary ‘operator+’

πάντα ῥεῖ · Accepted Answer · 2014-01-21 14:43:47Z

1

Looks like you a problem with VALUES statement:

VALUES ("+i,userName,password,j+");";

should be

"VALUES ("+std::to_string(i)+","+userName+","+password+","+std::to_string(j)+");";

If you don't have std::to_string (no C++11) you need some other way to convert int to std::string, like stringstream.

edited Jan 21, 2014 at 14:43

πάντα ῥεῖ

88.9k13 gold badges125 silver badges199 bronze badges

answered Jan 21, 2014 at 14:29

Wojtek Surowka

21.1k4 gold badges48 silver badges53 bronze badges

Comments

Digital_Reality · Accepted Answer · 2014-01-21 14:47:05Z

1

You can not build strings like this..

You have two options.. use std::string and form a string using + operator. Or if you would like to pursue with char* then use code below

char *sql = malloc (NumberOfCharYouNeed); //allocate sufficient chars for your purpose.

sprintf (sql, "INSERT INTO Login (_id,username,password,manager_id) VALUES (\"+%d%s%s%d+\")",i,userName,password,j);

Note: Make sure you free(sql) after your use.

answered Jan 21, 2014 at 14:47

Digital_Reality

4,8081 gold badge34 silver badges32 bronze badges

Collectives™ on Stack Overflow

syntax error with insert statement

5 Answers 5

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related