Need an advice of using quotes in SQLite request

[Vitaliy4us]

I know that the question is old but I still do not have any 100% working approach of using quotes in SQLite requests. In most cases the problem is solved in this way:

- if there are double quotes inside the request, the the request itself should be surrounded with the single quotes and vice verse

But often you can't know in advance what kind of quotes will be inside your request, or which is even more common, the request will contain the both types of quotes. What is the general approach in this case? For example, the request:

Local $request = "INSERT INTO tests (testId, testSummary, stepId, testStep, testData, testResult) VALUES (" & _
                    "'" & $testId & "'," & "'" & $testSummary & "', " & "'" & $stepId & "', " & "'" & $testStep & _
                    "', " & "'" & $testData & "', " & "'" & $testResult & "');"

can contain different types of quotes in any field. So how it should be composed to be correct in any case?

[genius257]

Hi @Vitaliy4us.

I would looking into binding variables to prepared statements.

to read more about variable binding, see: https://www.sqlite.org/c3ref/bind_blob.html

[Vitaliy4us]

43 minutes ago, Vitaliy4us said:

I know that the question is old but I still do not have any 100% working approach of using quotes in SQLite requests. In most cases the problem is solved in this way:

- if there are double quotes inside the request, the the request itself should be surrounded with the single quotes and vice verse

But often you can't know in advance what kind of quotes will be inside your request, or which is even more common, the request will contain the both types of quotes. What is the general approach in this case? For example, the request:

Local $request = "INSERT INTO tests (testId, testSummary, stepId, testStep, testData, testResult) VALUES (" & _
                    "'" & $testId & "'," & "'" & $testSummary & "', " & "'" & $stepId & "', " & "'" & $testStep & _
                    "', " & "'" & $testData & "', " & "'" & $testResult & "');"

can contain different types of quotes in any field. So how it should be composed to be correct in any case?

To be honest this is so complicated that it is much easier to convert the string with regular expression which can substitute the quotes to make them of one type. Is this the only way to solve the problem?

Edited October 25, 2019 by Vitaliy4us

[genius257]

Hmmm without binding a regex seems to be the best...

https://stackoverflow.com/questions/603572/escape-single-quote-character-for-use-in-an-sqlite-query

In this thread on stackoverflow they seem to do this. So i would suggest this StringRegExpReplace:

$input = "St. Mary's and St. John's"

$input = StringRegExpReplace($input, "'", "''")

Just tested the following sql:

SELECT 'this''s'

and the result is as exspected:

this's

 

Edited October 25, 2019 by genius257

[mikell]

A simple StringReplace is enough for this, or even better the dedicated func _SQLite_FastEscape()