Page 1 of 1
SQLite - preventing SQL injection
Posted: Sun Oct 11, 2015 11:43 pm
by tenbob
Hi. I am writing a data entry and retrieval program using SQLite. I have successfully connected to the database and read and written data using the built-in PB procedures.
I now want to add protection against SQL injection. From what I have read, the standard method is to parameterize the query. In other words, you should never let anything typed in by the user become part of the SQL query string. Instead compose the query with placeholders that are filled in later with the user data.
I understand the technique, but I don't know how to implement it in PB. I don't think it's possible using the built-in procedures. I may need to call the SQLite API directly using procedures like sqlite3_prepare_v2() and sqlite3_bind_*().
Has anyone done this before? I'd be grateful for any help.
Re: SQLite - preventing SQL injection
Posted: Mon Oct 12, 2015 12:21 am
by Kiffi
With the new PB Version 5.4 it is possible to bind Variables to Statements with the SetDatabase*()-Commands:
Code: Select all
SetDatabaseString(#Database, 0, "test")
If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=?")
; ...
EndIf
Greetings ... Peter
Re: SQLite - preventing SQL injection
Posted: Mon Oct 12, 2015 12:31 am
by Keya
With the new PB Version 5.4 it is possible to bind Variables to Statements with the SetDatabase*()-Commands
I think i saw freak mention this recently too in regards to stopping SQLi but i dont understand the how or why and im still fuzzy after your example
Could somebody please be kind enough to give example of a vulnerable query and show/explain how using a binded variable fixes the problem?
Thankyou!
Re: SQLite - preventing SQL injection
Posted: Mon Oct 12, 2015 2:06 am
by citystate
I find that URLEncoder() prevent most injection attempts - just need to remember to apply URLDecoder() to output strings
Re: SQLite - preventing SQL injection
Posted: Mon Oct 12, 2015 8:23 am
by Kiffi
Keya wrote:Could somebody please be kind enough to give example of a vulnerable query and show/explain how using a binded variable fixes the problem?
assuming you have the following construct:
Code: Select all
Username.s = GetGadgetText(#txtUsername)
Query.s = "Insert Into Employee (Username) Values ('" + Username + "')"
DatabaseUpdate(#Database, Query)
If #txtUsername contains a regular Username (like
Peter), you get this Query:
Insert Into Employee (Username) Values ('Peter')
... and everything is OK
But if the Username is:
Peter'); Drop Table Employee;--, you get:
Insert Into Employee (Username) Values ('Peter'); Drop Table Employee;--')
... and you lose your Employee-Table.
You can fix this by Binding Username to a Statement:
Code: Select all
Username.s = GetGadgetText(#txtUsername)
Query.s = "Insert Into Employee (Username) Values (?)"
SetDatabaseString(#Database, 0, Username)
DatabaseUpdate(#Database, Query)
Greetings ... Peter
Re: SQLite - preventing SQL injection
Posted: Mon Oct 12, 2015 8:39 am
by Keya
Code: Select all
Username.s = GetGadgetText(#txtUsername)
Query.s = "Insert Into Employee (Username) Values (?)"
SetDatabaseString(#Database, 0, Username)
DatabaseUpdate(#Database, Query)
Thankyou very much for the example! but please forgive me im having a blonde moment! i dont understand how that fixes the problem - if the #txtUsername field had your exploitative string example of "'Peter'); Drop Table Employee;" why does the exploit no longer work? (it seems like it would still be executing the same vulnerable query? but apparently not)
that's just the part im missing, sorry
Re: SQLite - preventing SQL injection
Posted: Mon Oct 12, 2015 12:35 pm
by deeproot
Because the entered data is now passed as a parameter to the query, no part of it can be treated as an additional SQL statement. It's just a data field and and any "injection" will probably be invalid.
It's quite a well known issue and certainly worth considering. PB now rightly offers a built-in solution, other methods, as above, can also be used. There's a detailed explained about all this on Wikipedia -
https://en.wikipedia.org/wiki/SQL_injection
Re: SQLite - preventing SQL injection
Posted: Wed Oct 14, 2015 3:05 pm
by tenbob
Thanks Kiffi. I have now downloaded 5.40 beta and successfully used this new feature. It's working very well !!
Thanks for pointing out that it's available in the beta. Otherwise I would have been completely stuck.