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.
SQLite - preventing SQL injection
Re: SQLite - preventing SQL injection
With the new PB Version 5.4 it is possible to bind Variables to Statements with the SetDatabase*()-Commands:
Greetings ... Peter
Code: Select all
SetDatabaseString(#Database, 0, "test")
If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=?")
; ...
EndIf
Hygge
Re: SQLite - preventing SQL injection
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 exampleWith the new PB Version 5.4 it is possible to bind Variables to Statements with the SetDatabase*()-Commands
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
I find that URLEncoder() prevent most injection attempts - just need to remember to apply URLDecoder() to output strings
there is no sig, only zuul (and the following disclaimer)
WARNING: may be talking out of his hat
WARNING: may be talking out of his hat
Re: SQLite - preventing SQL injection
assuming you have the following construct: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?
Code: Select all
Username.s = GetGadgetText(#txtUsername)
Query.s = "Insert Into Employee (Username) Values ('" + Username + "')"
DatabaseUpdate(#Database, Query)
... and everything is OKInsert Into Employee (Username) Values ('Peter')
But if the Username is: Peter'); Drop Table Employee;--, you get:
... and you lose your Employee-Table.Insert Into Employee (Username) Values ('Peter'); Drop Table Employee;--')
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)
Hygge
Re: SQLite - preventing SQL injection
Code: Select all
Username.s = GetGadgetText(#txtUsername)
Query.s = "Insert Into Employee (Username) Values (?)"
SetDatabaseString(#Database, 0, Username)
DatabaseUpdate(#Database, Query)
that's just the part im missing, sorry
-
- Enthusiast
- Posts: 271
- Joined: Thu Dec 17, 2009 12:00 pm
- Location: Llangadog, Wales, UK
- Contact:
Re: SQLite - preventing SQL injection
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
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
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.
Thanks for pointing out that it's available in the beta. Otherwise I would have been completely stuck.