SQLite User Forum

Not possible to escaping quotes(’’) with sqlite3_mprintf
Login
Chronological

Not possible to escaping quotes('') with sqlite3_mprintf

(1) By anonymous on 2022-07-01 08:01:15 [link] [source] 

with SQLite version 3.8.10.2
I have implemented system with SQL database.
It is based on C language so I use sqlite3 C APIs.

By using sqlite3_mprintf(),
there is some problem on escaping quotes('').

When I use REPLACE() function of SQL to remove comma(,) in string data from database, it is not possible to input right SQL query.

This is part of my source.

------------------------------------------------------------------
const char* comma = \',\';
const char *removeComma = \'\';
char *zSQL;
sqlite3_mprintf(zSQL, "SELECT * FROM table WHERE (REPLACE(colA||colB||colC, %Q, %Q) LIKE %%%q%%", comma, removeComma, searchWord));
------------------------------------------------------------------

With DB browser for SQLite or other SQLite application or online examples,
the SQL query, SELECT * FROM table WHERE (REPLACE(colA, ',', '')), works fine.
It shows result data without comma(,).

When I use this in C, quotes('') around comma(,) is not possible to indicate.
I have tried other ways to escape quotes('') with Substitution Types of SQLite3 and common escape for C by adding backslash in front of every single quote. 
However, there is no way to make correct SQL query.

When I print the SQL in zSQL to check it,
SQL query always shows SELECT * FROM table WHERE (REPLACE(colA, ,,))
so it is not possible to remove commas in string data.

I couldn't find any idea or way to input correct SQL for replacing comma(,).
Is this SQLite3 problem or my misunderstanding?

(2) By Ryan Smith (cuz) on 2022-07-01 08:39:35 in reply to 1 [link] [source]

I'm not an expert, but have you tried:

const char* comma = ",";
const char *removeComma = ""; // AFIK 'x' = Char, "x" = string (with \0 at end)

I'm not sure a char can be less than a char (empty string), but I'm probably mistaken, depending on flavour of C/C++/C# etc. Either way, those parameters are probably looking for strings.

Just a guess - Good luck!

(6) By anonymous on 2022-07-01 09:10:44 in reply to 2 [link] [source] 

Thank you for advice.
I make a array of char for comma and removeComma variables.

char comma[5];
char removeComma[5];

They are initialized as '\0' before strcat() is happened.

strcar(comma, "'");
strcat(comma, ",");
strcat(comma, "'");

strcat(removeComma, "'");
strcat(removeComma, "'");

They are printed by mprintf function.
However, it is same and it shows error.

SELECT * FROM table WHERE((REPLACE(colA||colB||colC, ,, ) LIKE searchWord);
This returns error with REPLACE function has wrong number of arguments.

(3) By Gunter Hick (gunter_hick) on 2022-07-01 08:42:38 in reply to 1 [link] [source] 

I think your C strings are wrong. Try "','" and "''" instead.
Why not put them directly into the statement string?

"SELECT * FROM table WHERE (REPLACE(colA, ',', '')) LIKE '%%%q%%'"

(4) By anonymous on 2022-07-01 08:50:26 in reply to 3 [link] [source] 

I have tried them already.
Same as SELECT * FROM table WHERE (REPLACE(colA, ,,)) LIKE %%%q%%

I think that the problem is escaping quotes.
They are all deleted when sqlite3_mprintf() is processed.

(5) By Ryan Smith (cuz) on 2022-07-01 08:54:22 in reply to 3 [link] [source]

Why not put them directly into the statement string?

I agree that seems best, but if perhaps the source is CSV related, I know some countries (like Holland) use semi-colon in stead of comma to separate (bust still calls it CSV rather than SSV!) which have occasioned me to use a variable to define the separator. Though, in that case, calling the variable "comma" is somewhat questionable.

(7) By anonymous on 2022-07-01 09:14:45 in reply to 1 [link] [source] 

Aynonymous post and reply need approval from server when it is posted.
Sorry for late response.

(8) By anonymous on 2022-07-01 12:00:29 in reply to 1 [link] [source] 

According to the current documentation, sqlite3_mprintf() takes the format string as the first argument:

char *sqlite3_mprintf(const char*,...);

Thus:

const char* comma = "','";
const char *removeComma = "''";

char *zSQL = sqlite3_mprintf("SELECT * FROM table WHERE (REPLACE(colA||colB||colC, %Q, %Q) LIKE %%%q%%", comma, removeComma, searchWord));

...

if (zSQL)
 sqlite3_free(zSQL);

To make it more clear:

const char* comma = ",";

char *zSQL = sqlite3_mprintf("SELECT * FROM table WHERE (REPLACE(colA||colB||colC, '%Q', '') LIKE %%%q%%", comma, searchWord));

(9) By anonymous on 2022-07-04 00:17:58 in reply to 8 [link] [source] 

Thanks for advice.
However, I already tried it and it didn't work.
Quotes are removed so REPLACE function doesn't work.

(10) By Larry Brasfield (larrybr) on 2022-07-04 02:58:30 in reply to 9 [link] [source]

I have found sqlite3_mprintf() to work as documented for all format strings I have seen in the sqlite3 shell source or written into code. Hence, I am highly skeptical that you have found a string that cannot be created with that API.

Looking through this thread, I do not see what string you are trying to create. Can you put it into a post here? If you do, I will demonstrate how to use sqlite3_mprintf() to create it. (Or, failing that, accept a bug report to the effect that string is beyond creation by sqlite3_mprintf().)

Please put your target string between triple-backtick lines so that it is replicated without interpretation by the forum renderer's markdown processor.

(11) By anonymous on 2022-07-04 04:56:56 in reply to 10 [link] [source]

What I want to process is to remove Comma from string of SQL database.

As I tested with SQLite, SQL Query, SELECT * FROM table WHERE (REPLACE(colA, ',', '')) LIKE searchWord, works.

However, for API of C/C++, the Query is not possible to create. It is always created "SELECT * FROM table WHERE (REPLACE(colA, ,, )) LIKE searchWord".

If I use formatted string of SQLite3 and Sustitution Types, no error occurs. If not, error occurs(wrong number of arguments REPLACE()).

What I want to create is below SQL query with comma and zero string between quotes.

SELECT * FROM table WHERE (REPLACE(colA, ',', '')) LIKE %%%q%%

(12) By Decker (d3x0r) on 2022-07-04 05:01:36 in reply to 11 [source]

the great thing about C using " for strings is you don't need to even do anything to ' to include it in a string. I really don't get where you're misunderstanding of C is... but I think you're blaming the wrong thing for it.

(13) By anonymous on 2022-07-04 05:15:55 in reply to 12 [link] [source]

However, when I try, the SQL doesn't work what I expect.

SELECT * FROM table WHERE (REPLACE(colA, ',', '') LIKE %%%q%%)

This is what I expect with using formatted string and Substitution Types. If the query is created correctly, the result should show data ignoring comma.

For example,

colA AB A,B A,,B A, B A,,, B

if AB is input for SELECT query, AB, A,B A,,B, A, B, A,,, B should be shown as result. However, it only shows AB.

When I test it with space, REPLACE(data, ' ', ''), it works fine. Space is removed from data correctly.

(14) By anonymous on 2022-07-04 06:09:23 in reply to 12 [link] [source]

C doesn't have any problem with including ' when I use " for strings. However, it might have something with sqlite3.

REPLACE function of sqlite3 doesn't work correctly with C/C++ API. If target string is comma(,), it could not replace it.

It doesn't work at all in any way to use escaping, to use Substitution of sqlite3, to input directly.

In command shell or sqlite3 application such as DB browser, SELECT query with REPLACE function works fine.

SELECT * FROM table WHERE(REPLACE(colA, ',', ,) LIKE '%input%');

(19) By anonymous on 2022-07-04 09:24:50 in reply to 10 [link] [source]

Thanks for checking all case. I checked my system setting and other things again and found that there was some problem.

After it is fixed, it works fine. Thank you.

(15) By anonymous on 2022-07-04 07:17:19 in reply to 9 [link] [source]

You are using %Q types (Q uppercase) in your format string, which by itself will quote (=double) all ' in your argument and "surround" it with one ' in at the beginning/end. So I think you get way to much doubled or quadrupled ' in your query.

Did you try with no pre-quoting and no quotes in your format string, like so;

const char* comma = ",";
...

char *zSQL = sqlite3_mprintf("... , %Q, ...", comma, ... );

Also, the LIKE %%%q%% part will produce LIKE %something% without quotes, LIKE '%%%q%%' would be needed.

Finally, you got parenthesis wrong (your C-code will prob. not compile): There is a missing closing ) at the end of your format string, but then there is one closing ) to much at the end for your sqlite3_mprintf line ...

(17) By anonymous on 2022-07-04 07:57:12 in reply to 15 [link] [source]

I didn't write in my main thread but I already tried them. '%q' or '%Q' all of them got same result without quotes around the word.

The code is part of my source. I've compiled and test it and checked log by printing SQL query to show how it is passed.

(16) By Gunter Hick (gunter_hick) on 2022-07-04 07:27:24 in reply to 1 [link] [source] 

This little source fragment

    const char word[]   = "%word%";
    const char comma[]  = ",";
    const char repl[]   = "";
    char       *sql     = NULL;

    sql = sqlite3_mprintf( "SELECT * FROM table where REPLACE(col,%Q,%Q) LIKE %Q;", comma, repl, word );
    printf( "%s\n", sql );

produces

SELECT * FROM table where REPLACE(col,',','') LIKE '%word%';

the same as this

    const char word[]   = "word";
    const char comma[]  = ",";
    const char repl[]   = "";
    char       *sql     = NULL;

    sql = sqlite3_mprintf( "SELECT * FROM table where REPLACE(col,%Q,%Q) LIKE '%%%q%%';", comma, repl, word );
    printf( "%s\n", sql );

(18) By anonymous on 2022-07-04 09:23:51 in reply to 16 [link] [source]

Thank you for advice. After your reply, I have checked my system where the code is worked. I found some issue in my system and it works fine with any of replace function case.

Thanks a lot.