CODEWITHSUNDEEP
c++qtsqlite
user63898
user63898

Reputation: 30915

how to deal with comma and apostrophe chars when inserting into SQLite

i have string that contains apostrophe and comma's and when i execute insert into SQLite it gives me error for example with string like this :

...., 'The Smiths - I Know It's Over', .....
"Over": syntax error Unable to execute statement

how can i or what can i do to keep the apostrophe's in the string but preform valid insert? im using Qt c++ .

Upvotes: 0

Views: 3077

Answers (3)

Mike Seymour
Mike Seymour

Reputation: 254531

You shouldn't be putting arbitrary strings directly into SQL - that's asking for an injection attack. Instead, use bound parameters; something like:

sqlite3_stmt * statement;
sqlite3_prepare(db, "select * from students where name=?", -1, &statement, NULL);
sqlite3_bind_text(statement, 1, "'; drop table students --", -1, SQLITE_STATIC);
sqlite3_step(statement);
sqlite3_finalize(statement);

This will replace the first parameter (?) in the query with the given string, with no danger of any ' character being interpreted as the end of the string, or of any part of the string being executed as SQL.

Upvotes: 7

user647772
user647772

Reputation:

From the FAQ:

(14) How do I use a string literal that contains an embedded single-quote (') character?

The SQL standard specifies that single-quotes in strings are escaped by putting two single quotes in a row. SQL works like the Pascal programming language in the regard. SQLite follows this standard. Example:

INSERT INTO xyz VALUES('5 O''clock');

Upvotes: 3

mdprotacio
mdprotacio

Reputation: 842

I believe the mysql library has a function named mysql_real_escape_string to escape the string. Also, you may use double quotes to surround the string or escape the apostrophe of your input string like this 'The Smiths - I Know It\'s Over',

Upvotes: 0

Related Questions