On Thu, 31 Dec 2009 10:19:24 -0500, Tim wrote:
> I have the need to prepare some strings for including
> into sqlite, I have not found an escape function, but I
> was wondering if anyone had any comments about the
> following code snippet. I had read on the site
> sqlite.org web site that ' should pretty much be the
> only problem character and that \ escaping doesn't
> "work" as might be expected. This function is basically
> just to make sure that any quotes in text fields still
> appear correctly. Is there anything else I might need?
> Thanks,
> Tim
Your question is more on topic in the sqlite-users mailing
list, sqlite-dev focusses on development of sqlite itself.
I forwarded your mail to sqlite-users.
In short: if you use the sqlite3_prepare*() and
sqlite3_bind*() interface you don't have to escape single
quotes. Only if you compose a complete SQL statement for the
deprecated sqlite3_exec() call and using the sqlite3 command
line tool, where single quotes are used for text literals,
you'd have to worry about escaping single quotes, which, by
the way can be done by just doubling it:
INSERT INTO person (name) VALUES ('O''Reilly');
> #include <stdio.h>
> #include <stdlib.h>
> #include <string.h>
> static char * sqlitedrv_escape_string(const char *instr) ;
> static char * sqlitedrv_unescape_string(const char * instr) ;
> int main ( ){
> char * sqlite = "this is a test' this is after''\n" ;
> char * out = sqlitedrv_escape_string ( sqlite ) ;
> char * back = NULL ;
> printf ( sqlite ) ;
> if ( out ){
> printf ( out ) ;
> back = sqlitedrv_unescape_string ( out ) ;
> if ( back ){
> printf ( back ) ;
> if ( strcmp ( sqlite, back ) == 0 ){
> printf ( "They are the same\n" );
> }else{
> printf ( "They are NOT the same\n" );
> }
> free ( back ) ;
> }
> free ( out ) ;
> }else{
> printf ( "Failed to escape\n" ) ;
> }
> return 0 ;
> }
> static char * sqlitedrv_unescape_string(const char * instr){
> char *outstr;
> char * ptr ;
> unsigned int len;
> unsigned int tlen = 0 ;
> unsigned int atlen = 0 ;
> unsigned int i = 0 ;
> if (instr == NULL)
> return NULL;
>
> len = strlen(instr);
> atlen = (len * sizeof(char)) + 1 ;
> outstr = ( char * ) malloc(atlen);
> if (outstr == NULL)
> return NULL;
> ptr = outstr ;
> for ( i = 0 ; i < len ; i++ ){
> if ( tlen >= atlen ){
> break ;
> }
> if ( instr[i] == '\'' && instr[i+1] == '\'' ){
> i++;
> tlen ++ ;
> }
> *ptr = instr[i] ;
> ptr++ ;
> tlen ++ ;
> if ( instr=='\0' ){
> break ;
> }
> }
> *ptr = '\0' ;
> return outstr;
> }
>
> static char * sqlitedrv_escape_string(const char *instr) {
> char *outstr;
> char * ptr ;
> unsigned int len;
> unsigned int tlen = 0 ;
> unsigned int atlen = 0 ;
> unsigned int i = 0 ;
> if (instr == NULL)
> return NULL;
> len = strlen(instr);
> atlen = (2 * len * sizeof(char)) + 1 ;
> outstr = ( char * ) malloc(atlen);
> if (outstr == NULL)
> return NULL;
> ptr = outstr ;
> for ( i = 0 ; i < len ; i++ ){
> if ( tlen >= atlen ){
> break ;
> }
> if ( instr[i] == '\'' ){
> *ptr = '\'' ;
> ptr++ ;
> tlen ++ ;
> }
> *ptr = instr[i] ;
> ptr++ ;
> tlen ++ ;
> if ( instr=='\0' ){
> break ;
> }
> }
> *ptr = '\0' ;
> return outstr;
> }
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
