AutoHotkey Community

It is currently May 27th, 2012, 12:55 pm

All times are UTC [ DST ]




Post new topic Reply to topic  [ 79 posts ]  Go to page Previous  1, 2, 3, 4, 5, 6  Next
Author Message
 Post subject:
PostPosted: January 3rd, 2012, 10:13 pm 
Offline
User avatar

Joined: May 10th, 2007, 10:54 am
Posts: 649
Location: .switzerland
I've added string escaping to the autogenerated insert sql statements, to prevent unexpected sql injections etc.

The Database now has an Method called EscapeString(str), which can easily accessed by:
Code:
;Database db
 
username := "x'; DROP TABLE members; --"
username  := db.EscapeString(username)

_________________
http://securityvision.ch
AHK 2D GAME ENGINE


Report this post
Top
 Profile  
Reply with quote  
PostPosted: January 25th, 2012, 6:30 pm 
Offline

Joined: November 21st, 2008, 6:53 am
Posts: 2
My problem here was that I dragged the DBA files directly out of WinRar into my working directory, without creating \lib\ subdirectory. Everything works fine. I'm using AHK 1.1.05.06_L 32 bit Unicode on Windows 7 Home Premium 64. Thanks again for DBA 0.8!

figure8car wrote:
Thanks for all this work. I'm looking forward to using SQLite with AutoHotKey! I'm usingon Windows 7 64. When I try to run this sample code on a database that I'm now using in a Python program, I have to remove the <> symbols in the #Include statements and append the Include file name with .ahk. If I do that, it stops complaining. Also, the database I'm using has a file extenstion of .db instead of .sqlite. It works in Python and with SQLite Manager in FireFox. Does that matter here? The reason I ask is because no error messages occur until I try to read some data, then I get "A non object value was improperly invoked"

Then in the diagnostic window:
045: columnCount := tables.Columns.Count()
091: ListLines

A problem reading the database?


[/img]


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: January 29th, 2012, 3:08 pm 
Offline
User avatar

Joined: May 10th, 2007, 10:54 am
Posts: 649
Location: .switzerland
The user AimLike extracted the 64bit libmySQL.dll and I included it into the DBA library. DBA automatically detects the AHK Version running and chooses the right dll.
Though, out of box support for 64bit SQLite is still missing. Anyway, DBA has been updated to version 0.9. (I'm running out of alpha version numbers :mrgreen:)


If some of you wish to include a very basic ORM, where you can also UPDATE you Objects/Records, I may give it a try.

Quote:
Thanks again for DBA 0.8!

Thank you for the kind words.

_________________
http://securityvision.ch
AHK 2D GAME ENGINE


Report this post
Top
 Profile  
Reply with quote  
 Post subject: Thanks
PostPosted: January 31st, 2012, 5:27 pm 
Offline

Joined: January 22nd, 2012, 6:10 pm
Posts: 5
This is an awesome library!


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: March 1st, 2012, 11:05 am 
Offline

Joined: October 5th, 2011, 11:32 pm
Posts: 66
Location: Poland
If i have situation that ahk have utf-8 coding, but my database have iso 8859-2 and i want send select to it, how can i send query with some polish chars?

Something like that will work:
Code:
Select * from table1


Something like that will not (0 rows):
Code:
Select * from table1 where table1.name = 'święty'


How can I encode string with this query to iso 8859-2 that i will have some effect???


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: March 4th, 2012, 2:45 pm 
Hi,
I have two problems at the moment.
The first is, how can I insert variables in the database?
Code:
   record := {}
   record.Name := "%name%"
   record.Passwort := "%loginpassword%"
   db.Insert(record, "accounts")

that's not working, i do not know, how to write that, that it will save the variables.

The second thing is, how can I check if there is already an account with the same name?
Is there any description of the usage?

Mash


Report this post
Top
  
Reply with quote  
 Post subject:
PostPosted: March 5th, 2012, 1:14 pm 
Offline
User avatar

Joined: May 10th, 2007, 10:54 am
Posts: 649
Location: .switzerland
Code:
record.Name := "%name%"
record.Passwort := "%loginpassword%"

Your expression syntax is wrong, unquoted literals are considered as variables in a expression. Please re-read the AHK help topic expressions.

Code:
record.Name := name
record.Passwort := loginpassword


Quote:
The second thing is, how can I check if there is already an account with the same name?

Use pure SQL, the Library does not offer any ORM which does check if an Enitity is alread existing in the DB. Use a SQL Where-clause.


Thus, something like that works to check that:

Code:
username :=  "Max"
foundRow := db.QueryRow("Select * from accounts Where Name = "  db.EscapeString(username))

if(IsObject(foundRow ))
  msgbox % "Whoohoo we have a user with the name: " foundRow.Name
else
 msgbox  % "I'm sorry but there is no one called " username


However, I recommend to use ID keys, best might be GUIDs which can be generated client side for a proper Enitity handling.

@muisek:
Quote:
How can I encode string with this query to iso 8859-2 that i will have some effect???

You may encode the whole SQL String in the proper format and pass it to the Query Method. But there are a lot of pitfalls, for example the diffrent db libraries and their support for those encodings.

_________________
http://securityvision.ch
AHK 2D GAME ENGINE


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: March 5th, 2012, 9:39 pm 
I am getting a lot of ERRORLEVEL =5 errors when using sqlite while running several inserts and selects in rapid succession. Could this be a bug in the library, or is it just a function of sqllite?


Report this post
Top
  
Reply with quote  
 Post subject:
PostPosted: March 7th, 2012, 1:22 pm 
Offline
User avatar

Joined: May 10th, 2007, 10:54 am
Posts: 649
Location: .switzerland
cubesnyc wrote:
I am getting a lot of ERRORLEVEL =5 errors when using sqlite while running several inserts and selects in rapid succession. Could this be a bug in the library, or is it just a function of sqllite?


Code:
#define SQLITE_BUSY         5   /* The database file is locked */


SQLite is single user only, and strictly atomic.

Thus you can have just one Connection which has write permissions and you can not do parallel Querys further you should close any Resultset before using a new one. Most issues using SQLite occur if you do not close Resultsets before opening a new one.


You may just catch those errors and retry if they occur not by design.

You may post your Code part (for ex the loop where you to those querys)

_________________
http://securityvision.ch
AHK 2D GAME ENGINE


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 10th, 2012, 11:40 am 
Offline

Joined: October 5th, 2011, 11:32 pm
Posts: 66
Location: Poland
Is there any option to cache MySql queries??


Report this post
Top
 Profile  
Reply with quote  
PostPosted: April 10th, 2012, 1:54 pm 
Offline
User avatar

Joined: May 10th, 2007, 10:54 am
Posts: 649
Location: .switzerland
Jup, the C-API(which is internally used by this wrapper) supports prepared statements, as you can see here: http://dev.mysql.com/doc/refman/5.1/en/ ... ments.html

However, it is currently not implemented and I decided to stop any development on AHK Scripts as long as the Future of the advanced Features of AutoHotkey is not preserved.

_________________
http://securityvision.ch
AHK 2D GAME ENGINE


Report this post
Top
 Profile  
Reply with quote  
PostPosted: April 10th, 2012, 2:39 pm 
Offline

Joined: October 13th, 2009, 10:09 pm
Posts: 1389
Very comprehensible. It's quite sad that the future plans for AHK appear like a few steps backwards.


Report this post
Top
 Profile  
Reply with quote  
PostPosted: April 16th, 2012, 3:51 am 
Offline

Joined: March 27th, 2008, 2:14 pm
Posts: 700
I just fixed a very obscure and difficult to fix bug.

Somewhere (not sure where exactly), the entire contents of the Row class was completely cleared effectively making new Row() actually call new Collection() instead (since Row extends Collection, the call to __new fell through). However this only happened on the second call... very frustrating. Renaming the class (and references to it) to DBRow fixed it for now.

This should be a lesson in class naming:
Quote:
Never name a class anything that could conceivably be used as a regular variable name.

Use long and/or very specific names for your classes OR use subclasses. Class names like Row, Table, Database, RecordSet etc is just inviting disaster.

Edit: On a second look, it appears I used a local variable named row in one of my functions but since classes are super-global it still referenced the class, so this is technically not a bug in the library itself, but it's still inviting disaster.

Edit 2: On third look, I actually declare that variable local... so nfi what's going on.

_________________
Scripts - License


Report this post
Top
 Profile  
Reply with quote  
 Post subject: Re:
PostPosted: April 16th, 2012, 5:46 am 
Offline

Joined: February 6th, 2007, 12:30 am
Posts: 142
Location: Michigan
IsNull is already aware of the need to prefix class names. I'm sure he'll get to it when he can.
panofish wrote:
Please prefix the classes.
It takes a while to debug these errors which are caused by using a variable of the same name as a class.

This is an excerpt of my code that was failing.
The get for rec["status"] failed with a "non-object value invoked".
It took me a while to discover that my use of the variable "row" was causing the error, just like my previous use of a variable called "table".

Code:
PickList2:
       
    if IgnoreEvent()
        return
   
    gui_status("","clear.ico")    ; clear statusbar
   
    ;------------------------------------------------
    ; get request id from selected row
    ;------------------------------------------------   
       
    Gui, ListView, LIST2    ; specify which listview
   
    row := LV_GetNext("")   ; get selected row number
   
    if (row = 0)     ; return if no row selected
        return

    LV_GetText(requestid, row, 1)  ; Get the text from specified column
   
    ;------------------------------------------------
    ; update form fields for selected request id
    ;------------------------------------------------   

    ; use requestid to get all data from request table
         
    sql =
    (
         select status,
                DATE_FORMAT(opendate, '`%m-`%d-`%Y') as opendate,
                DATE_FORMAT(duedate, '`%m-`%d-`%Y') as duedate,
                DATE_FORMAT(closedate, '`%m-`%d-`%Y') as closedate,
                requesttype,
                concat(modelyear, " ", brand, " ", make) as vehicle,
                description,
                priority
           from request
          WHERE requestid = %requestid%
    )   

    rec := db.QueryRow(sql)
    status := rec["status"]
    opendate := rec["opendate"]
    ...

_________________
http://www.panofish.net


Report this post
Top
 Profile  
Reply with quote  
PostPosted: April 16th, 2012, 6:19 am 
Offline

Joined: March 27th, 2008, 2:14 pm
Posts: 700
Ah I see. Sorry for posting on a problem that has been reported before. :)

_________________
Scripts - License


Report this post
Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 79 posts ]  Go to page Previous  1, 2, 3, 4, 5, 6  Next

All times are UTC [ DST ]


Who is online

Users browsing this forum: hughman, rrhuffy and 2 guests


You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Powered by phpBB® Forum Software © phpBB Group