Sign in to follow this  
Followers 0
jchd

Usefullness of $iBufferSize parameter in _SQLite_Escape

9 posts in this topic

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites



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.


GDIPlusDispose - A modified version of GDIPlus that auto disposes of its own objects before shutdown of the Dll using the same function Syntax as the original.EzMySql UDF - Use MySql Databases with autoit with syntax similar to SQLite UDF.

Share this post


Link to post
Share on other sites

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by KaFu

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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... :)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0