Problem with quotes in a string-variable passed to SQLite

[which1]

Hi there,

I'm experiencing the following problem :

This works fine :

_SQLite_Exec(-1, "UPDATE table1 SET column1 = 'Some text' WHERE table1_ID = 2")

Now, take $txt is a string variable : $txt = "Some text"

Of course this works fine too :

_SQLite_Exec(-1, "UPDATE table1 SET column1 =" & " ' " & $txt & " ' " & " WHERE table1_ID = 2")

But the following $txt raises an issue due to the quote inside the text :

$txt = "It's a text"

Double quote doens't help : Either the quote appears in the string command ("UPDATE table1...) and it's causing an error

Or it doens't appear at all, and the text is wrong (missing it's quotes).

Furthermore, $txt use to be : $txt = FileRead("C:\Programm\export_text_SQLite.txt") and use to be round 1Kb

In this case, quotes are likely to be met everywhere in the text.

The solution I use for now is :

Local $txt = StringReplace($texte, "'", "*") before input in the Base

Local $out = StringReplace($texte, "*", "'") after output from the Base

But this sounds at lest artificial and tricky

[KaFu]

Use _SQLite_FastEscape($txt), this function in principle does what you're currently doing manually.

Edited December 4, 2012 by KaFu

[jchd]

Adding to KaFu's corrcet answer, you only need to escape string inputs (INSERTs).

SELECT queries results don't need any kind of adjustment.

[which1]

Thanks to both of you. Very helpful indeed !

[Mat]

Adding to KaFu's corrcet answer, you only need to escape string inputs (INSERTs).

SELECT queries results don't need any kind of adjustment.

Heh. I can see this going very wrong when people find a way to read tables they shouldn't be. A lot of SQL implementations allow you to end a line with ; and then start a new statement in the same query.

So I think it should stay as escape all user input before querying anything.

[jchd]

Sorry Mat but I don't fully get what you just say.

In a sense I used a bad phrasing: every string literal used in a SQLite statement should be escaped (= meaningful single quotes must be doubled). While this is most useful for inserts or updates, it is also required for string literals in other SQL statements.

I nonetheless stand behind the fact that queries result don't need any adjustment.

Yes, SQLite allows multiple SQL statements in a single _SQLite_Exec call. However I fail to see what this has to do with escaping string literals.

[Mat]

Ah ok, so you mean data returned? I thought you meant something like this was ok:

_SQLite_Exec("SELECT * FROM foo WHERE bar = '" & $userInput & "'")

Where $userInput has not been escaped. That would be opening up a hole for an injection attack.

Re-reading what you posted, you are right. I read it as "SELECT queries don't need any kind of adjustment. "

[jchd]

Zero problem Mat!