Usefullness of $iBufferSize parameter in _SQLite_Escape

[jchd]

The current situation is as follows:

In the SQLite UDF the function _SQLite_Escape has an optional second parameter $iBufferSize, which is currently documented to somehow limit the size of the escaped string returned.

It turns out that this parameter is essentially useless because of a ugly overlook in implementation. It truncates the string once escaped, which is plain wrong.

_SQLite_Escape('abcdef', 4) returns

'abc

The missing closing quote makes the result invalid for direct inclusion in an SQL statement.

I assume nobody ever used that parameter, since no bug report appeared on track about this.

There are two way to interpret what was initially intended for the usage of this parameter:

• limit the size of the input string before it is escaped
• limit the size of the escaped string itself

In case 1.) I see no good point leaving the feature since it is barely never used and one can very easily use _SQLite_Escape(StringLeft($str, xxx)) or a similar construct to effectively limit the output string to some size around xxx characters (due to possible quote expansion in the escaping process).

In case 2.) we have a problem and need to loop in the input string character by character, a highly inefficient way to proceed.

The point is that interpretation 1.) is easy to obtain explicitely (as explained before) whenever it is needed, while interpretation 2.) doesn't seem to make any sense at all. "I want to escape a long string but I want the escaped string to be no longer than xxx" serves no practical purpose I can think of.

That's why I'd like your opinion before removing this _unused_ parameter. Keep in mind that SQLite is very flexible in accepting about whatever size or type you whish to feed it as input. It makes no difference storing a 25-char name or a 28-char name in any data column (except integer primary keys, as you know).

TIA for giving your opinion.

[Yoriz]

As someone that uses the sqlite udf, i cant say i've ever even used _SQLite_Escape at all, although to be honest i mainly just used _SQLite_SQLiteExe because when i started using sqlite in autoit the dll version of sqlite did'nt perform as fast as the exe, the dll is much better now so i have been using it in current projects.

[jchd]

Good, and the next-to-come revision will be a bit faster as well for some operations. Thanks for answering anyway.

[ptrex]

@jchd

Never had a need to use this function either.

Tested it and indead seems to be bugged.

Rgds

ptrex

[KaFu]

Never used it either, so removing seems fine... but looking at it, it makes sense to check where to use it , I might do it the hard way at one point or another ...

Edited April 8, 2010 by KaFu

[jchd]

In the next-to-come UDF version there are a couple of faster and simpler functions that aren't buggy and less error-prone.

_SQLite_FastEscape to escape a string (it will tolerate a numeric on input but that's not recommended unless specific need)

_SQLite_FastEncode to encode a binary variant.

I personally would like them to replace existing function directly, but who knows, there must be running code relying on the old akward stuff.

[PsaltyDS]

The problem is in the internal _SQLite_szStringRead():

Func __SQLite_szStringRead($iszPtr, $iMaxLen = -1)
    
    ; ...
    
    If $iMaxLen <= 0 Then
        Return SetError($err, @extended, $rtn)
    Else
        Return SetError($err, @extended, StringLeft($rtn, $iMaxLen))
    EndIf
EndFunc   ;==>__SQLite_szStringRead

But that's not where it should be fixed, in case that function is used elsewhere and the truncation should be as is. So back to _SQLite_Escape(), where this is the fix:

Func _SQLite_Escape($sString, $iBuffSize = Default)
    If $g_hDll_SQLite = 0 Then Return SetError(1, $SQLITE_MISUSE, "")
    If IsNumber($sString) Then $sString = String($sString)  ; to help number passing common error
    Local $tSQL8 = __SQLite_StringToUtf8Struct($sString)
    If @error Then Return SetError(2, @error, 0)
    Local $aRval = DllCall($g_hDll_SQLite, "ptr:cdecl", "sqlite3_mprintf", "str", "'%q'", "ptr", DllStructGetPtr($tSQL8))
    If @error Then Return SetError(1, @error, "") ; Dllcall error
    If IsKeyword($iBuffSize) Or $iBuffSize < 1 Then $iBuffSize = -1
    Local $sResult = __SQLite_szStringRead($aRval[0], $iBuffSize)
    If @error Then Return SetError(3, @error, "") ; Dllcall error

    ; ----------------------------------------------------------------------------------------
    ; Fix bug where closing escape character gets truncated
    ; ----------------------------------------------------------------------------------------
    If StringLeft($sResult, 1) <> StringRight($sResult, 1) Then $sResult &= StringLeft($sResult, 1) ; Restore closing escape character if it got trimmed off
    ; ----------------------------------------------------------------------------------------
    
    DllCall($g_hDll_SQLite, "none:cdecl", "sqlite3_free", "ptr", $aRval[0])
    Return $sResult
EndFunc   ;==>_SQLite_Escape

I've never used it, but can see the value of the function. Of course faster is better, but this will fix the existing one. Test with the following demo:

#include <SQLite.au3>
#include <SQLite.dll.au3>

Global $sTestString,$i,$aRow

_SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)

For $i = 1 To 255
    $sTestString &= Chr($i)
Next
ConsoleWrite("Before: $sTestString = " & $sTestString & @LF)
$sEscString = _SQLite_Escape($sTestString, 64)
ConsoleWrite("After:  $sEscString = " & $sEscString & @LF)

_SQLite_Shutdown()

P.S. Quoting:

That's why I'd like your opinion before removing this _unused_ parameter. Keep in mind that SQLite is very flexible in accepting about whatever size or type you whish to feed it as input. It makes no difference storing a 25-char name or a 28-char name in any data column (except integer primary keys, as you know).

I didn't think of the utility of this function as about qualifying a string for input to SQLite, but rather for the DBM to maintain max col width of data put in a table.

But for that purpose this applies as well:

...one can very easily use _SQLite_Escape(StringLeft($str, xxx)) or a similar construct

So maybe that doesn't make any difference. Edited April 8, 2010 by PsaltyDS

[jchd]

Dear Penguin, sorry but your fix doesn't work. Try with both $n = 6 and 7: _SQLite_Escape("abc'''''''''''", $n)

Appending a quote right depends on the even parity of the sequence of right quotes, if any.

My point is that it's a mess if we do it this way. StringLeft-ing alongish string before calling is in my view the best way to go.

Than we can have more efficiency for 99.99% of the cases.

[PsaltyDS]

Dear Penguin, sorry but your fix doesn't work. Try with both $n = 6 and 7: _SQLite_Escape("abc'''''''''''", $n)

Appending a quote right depends on the even parity of the sequence of right quotes, if any.

My point is that it's a mess if we do it this way. StringLeft-ing alongish string before calling is in my view the best way to go.

Than we can have more efficiency for 99.99% of the cases.

I totally missed that. Rats.

You only brought that up because your RC circuit is shorted out...