CODEWITHSUNDEEP
sqlite
Tmacy
Tmacy

Reputation: 1

How to insert a " into sqlite3 text

I want to insert a string ,like Hello ,I am "Tmacy"!,into a sqlite3 table.But I can't find a way to insert the ".(ps.I tried to use \" instead of ", but it doesn't word.). Note:I use the C/C++ API function sqlite3_exec to insert the string into sqlite table.If you insert it with the sqlite3 command , it may works .

like that:

 sprintf(sqlcmd,"insert into dict values('%s','%s')",word,meaning);
 if(sqlite3_exec(data,sqlcmd,NULL,NULL,&errmsg) != SQLITE_OK){
     printf("insert error!:%s\n",errmsg);
 }else{
     printf("insert success!\n");
 }

Thanks!

Upvotes: 0

Views: 1939

Answers (3)

ShadowLiberal
ShadowLiberal

Reputation: 348

You'd be better off using parameters here, it's safer then simply making slight changes to the text strings so that " and such don't cause a crash. Some sample code of using parameters in one of my own projects, in VB .net.

Public Sub RunSQLiteCommand(ByVal CommandText As String, Optional ByVal ReadDataCommand As Boolean = False, Optional ByVal ParameterList As Hashtable = Nothing)
            SQLcommand.Parameters.Clear()
            SQLcommand.CommandText = CommandText
            If ParameterList IsNot Nothing Then
                For Each key As String In ParameterList.Keys
                    SQLcommand.Parameters.Add(New SQLite.SQLiteParameter(key, ParameterList(key)))
                Next
            End If
            SQLcommand.ExecuteNonQuery()
            If ReadDataCommand Then
                SQLreader = SQLcommand.ExecuteReader()
            End If
End Sub

If you really want to avoid using parameters though, then "" (two of them) would be the way to go. If I'm ever not using parameters I run all string values I'm inserting into the database through a function that replaces " with "" everywhere in the string value.

Edit: Oh yeah, I almost forgot. For using parameters, your key value should be '@FieldName', and your SQL Query should say '@FieldName' wherever you use that parameter.

So for example if you call your field 'Name' that you want to insert the " in, you'd have something like this for insert query.

insert into dict values(@Name, @OtherField)

You could call your parameter anything you want, it's just easier if you name it the same thing as the field you're loading the value into.

Upvotes: 0

Himanshu
Himanshu

Reputation: 2454

Try repeating the quote instead of escaping it :-

http://www.sqlite.org/faq.html#q14

Upvotes: 0

CL.
CL.

Reputation: 180182

The sqlite3_mprintf function has formats like %Q that allow you to format strings correctly:

char *sql = sqlite3_mprintf("INSERT INTO dict VALUES(%Q,%Q)", word, meaning);
err = sqlite3_exec(data, sql, NULL, NULL, &errmsg);
sqlite3_free(sql);

Upvotes: 2

Related Questions