sqllite query with encrypted std::string (unrecogn

Question

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?

Answer 1

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.