jchd Posted April 7, 2010 Share Posted April 7, 2010 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'abcThe 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 escapedlimit the size of the escaped string itselfIn 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
Yoriz Posted April 8, 2010 Share Posted April 8, 2010 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. Link to comment Share on other sites More sharing options...
jchd Posted April 8, 2010 Author Share Posted April 8, 2010 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
ptrex Posted April 8, 2010 Share Posted April 8, 2010 @jchd Never had a need to use this function either. Tested it and indead seems to be bugged. Rgds ptrex Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM - New Link to comment Share on other sites More sharing options...
KaFu Posted April 8, 2010 Share Posted April 8, 2010 (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 April 8, 2010 by KaFu  OS: Win10-22H2 - 64bit - German, AutoIt Version: 3.3.16.1, AutoIt Editor: SciTE, Website: https://funk.eu AMT - Auto-Movie-Thumbnailer (2022-Nov-26) BIC - Batch-Image-Cropper (2023-Apr-01) COP - Color Picker (2009-May-21) DCS - Dynamic Cursor Selector (2024-Feb-16) HMW - Hide my Windows (2018-Sep-16) HRC - HotKey Resolution Changer (2012-May-16) ICU - Icon Configuration Utility (2018-Sep-16) SMF - Search my Files (2023-Jun-03) - THE file info and duplicates search tool SSD - Set Sound Device (2017-Sep-16) Link to comment Share on other sites More sharing options...
jchd Posted April 8, 2010 Author Share Posted April 8, 2010 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
PsaltyDS Posted April 8, 2010 Share Posted April 8, 2010 (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 April 8, 2010 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 Link to comment Share on other sites More sharing options...
jchd Posted April 8, 2010 Author Share Posted April 8, 2010 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
PsaltyDS Posted April 9, 2010 Share Posted April 9, 2010 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now