3

I have a C++ std::string which is encrypted using AES128 and want to write it into a sqllite database. I figured out already, that I have to escape ' characters with '' and " with "", but there seems to be another problem.

It says:

unrecognized token: "'""\235\211g\264\376\247\3348( ]tu\202\346\360\226h\205D\322-\373\347y"

My query looks like:

UPDATE tablename
SET column='""\235\211g\264\376\247\3348( ]tu\202\346\360\226h\205D\322-\373\347y\315\|`\3206\245\220j6
\215&\301ww/\222R\352]\253,\362&\233ï\2530\322搜\377\321!\334t\224\271ќVu\214Z\\256""\242O\254\241\254\365\360<P\364\356\370\225jnۢ\231\335($\243\377fH\225\215\224\223\254\316' 
WHERE index='1';

The same query with the unencrypted string works. Any ideas?

Constantin
  • 8,721
  • 13
  • 75
  • 126
  • Are you generating this query in C++ code? If so, what compiler do you use - because some support raw string literals. – mockinterface Feb 02 '14 at 12:32
  • @mockinterface Yes, I'm using LLVM 5.0 and have to use the `c_str()` function from `std::string` to passing the statement to the libsqllite function (`sqlite3_prepare_v2()`). But even if I tried to execute the statement in an sqllite manager, the query doesn't work. The string is generated from a `std::array output` using the constructor `std::string(std::begin(output), std::end(output));` – Constantin Feb 02 '14 at 12:52

1 Answers1

4

You are doing it wrong.

You should not, ever, write out the parameters in full within the query; but instead you should use bound parameters: Binding Values To Prepared Statements.

The main advantage ? Bound parameters do not have to be escaped, which completely prevents any risk of SQL injections, and also greatly simplifies your life!

Also, prepared statements can be reused for greater efficiency, so let me give a full example.

//
// WARNING: for concision purposes there is no error handling
//          and no attempt at making this code even remotely exception-safe.
//
// !!! DO NOT USE IN REAL LIFE !!!
//
void update(std::map<int, std::string> const& blobs) {
    // 1. Prepare statement
    sqlite3_stmt *stmt;

    sqlite3_prepare(db, 
                    "update tablename set column = ? where index = ?",
                    -1, // statement is a C-string
                    &stmt,
                    0  // Pointer to unused portion of stmt
    );

    // 2. Use statement as many times as necessary
    for (auto const& pair: blobs) {
        int const index = pair.first;
        std::string const& blob = pair.second;

        // 2.1 Bind 1st parameter
        sqlite3_bind_text(stmt,
                          1,  // 1-based index: 1st parameter
                          blob.data(),
                          blob.size(),
                          0   // no need for sqlite to free this argument
        );

        // 2.2 Bind 2nd parameter
        sqlite3_bind_int(stmt,
                         2, // 1-based index: 2nd parameter
                         index
        );

        // 2.3 Execute statement
        sqlite3_step(stmt);

        // 2.4 Reset bindings
        sqlite3_reset(stmt);
    }

    // 3. Free prepared query
    sqlite3_finalize(stmt);
} // update

Note: you can of course keep the prepared statement around for an even longer time.

Matthieu M.
  • 287,565
  • 48
  • 449
  • 722
  • Oh, I wasn't aware of that! I got it working with this code - thank you! – Constantin Feb 02 '14 at 14:29
  • 1
    @Constantin: I am glad I could help you, I do encourage you to try and create a C++ oriented interface over sqlite though (for example using exception to signal errors and automatically calling the `sqlite3_reset` and `sqlite3_finalize`). It would make a good exercise :) – Matthieu M. Feb 02 '14 at 14:58
  • I am facing this problem while inserting a google protobuf encoded string, which gives "unrecognized token". This is very helpful answer. However, imagine that if we are dealing with a little old code which is so tightly coupled with `sqlite3_exec()` at most places. Of course we can change it with `prepare() , bind(), step()` combination. But is there any way, with `exec()` itself to insert such string encoded string? – iammilind Dec 07 '17 at 11:19
  • @iammilind: All things told, it'll probably take you less time to replace all uses of exec by prepare+bind than attempting to debug other solutions; it's fairly mechanical. If you insist on using `exec`, do not seek to "escape", it's much too error-prone, instead use Base64. – Matthieu M. Dec 07 '17 at 11:25
  • Actually, I didn't know sqlite, when I had to start with it. To avoid the boiler plate code & ease of use, I created a library of my own, which understands C++ structures, parses them and converts their relevant variables into respective SQL types. E.g. if I have sqlite enabled `class C` which has some variables as sqlite columns, then the statement will simply look as `mySqliteDB.INSERT(c)` (`c` is an object of C). Rest of the things will be taken care by my library. Similarly `SELECT` will return a C++ vector or map. Effectively, I have only 1 exec statement, but everything is around it. :-) – iammilind Dec 07 '17 at 11:32
  • @iammilind: In this case you should be in a good position to move toward bound statements. – Matthieu M. Dec 07 '17 at 11:40
  • For education purpose, would you like to look & answer this Qn: [SQLite unrecognized token error while inserting protobuf: Which tokens to be handled & How?](https://stackoverflow.com/q/47715711/514235). It seems that I am facing issue with NUL (ascii: 0) character. – iammilind Dec 09 '17 at 06:02