It is currently Sun Feb 17, 2019 1:11 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 8 posts ] 
Author Message
 Post subject: SQLite - preventing SQL injection
PostPosted: Sun Oct 11, 2015 11:43 pm 
Offline
New User
New User

Joined: Sun Oct 11, 2015 11:13 pm
Posts: 2
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite - preventing SQL injection
PostPosted: Mon Oct 12, 2015 12:21 am 
Offline
Addict
Addict
User avatar

Joined: Tue Mar 02, 2004 1:20 pm
Posts: 997
Location: Amphibios 9
With the new PB Version 5.4 it is possible to bind Variables to Statements with the SetDatabase*()-Commands:

Code:
SetDatabaseString(#Database, 0, "test") 
If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=?")   
  ; ...
EndIf


Greetings ... Peter

_________________
Can't decide if i need a hug, an XXL coffee, 6 shots of vodka or 2 weeks of sleep.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite - preventing SQL injection
PostPosted: Mon Oct 12, 2015 12:31 am 
Offline
Addict
Addict
User avatar

Joined: Thu Jun 04, 2015 7:10 am
Posts: 1673
Quote:
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! :)

_________________
Thankyou to all the coders who generously helped & encouraged me in the nearly 2yrs when i was welcome here,
it was a tremendous privilege. I learned a lot. I wish you and your families all the best and success for the future.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite - preventing SQL injection
PostPosted: Mon Oct 12, 2015 2:06 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Sun Feb 12, 2006 10:06 pm
Posts: 638
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


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite - preventing SQL injection
PostPosted: Mon Oct 12, 2015 8:23 am 
Offline
Addict
Addict
User avatar

Joined: Tue Mar 02, 2004 1:20 pm
Posts: 997
Location: Amphibios 9
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:
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:
Quote:
Insert Into Employee (Username) Values ('Peter')
... and everything is OK


But if the Username is: Peter'); Drop Table Employee;--, you get:
Quote:
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:
Username.s = GetGadgetText(#txtUsername)
Query.s = "Insert Into Employee (Username) Values (?)"
SetDatabaseString(#Database, 0, Username)
DatabaseUpdate(#Database, Query)


Greetings ... Peter

_________________
Can't decide if i need a hug, an XXL coffee, 6 shots of vodka or 2 weeks of sleep.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite - preventing SQL injection
PostPosted: Mon Oct 12, 2015 8:39 am 
Offline
Addict
Addict
User avatar

Joined: Thu Jun 04, 2015 7:10 am
Posts: 1673
Code:
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 :)

_________________
Thankyou to all the coders who generously helped & encouraged me in the nearly 2yrs when i was welcome here,
it was a tremendous privilege. I learned a lot. I wish you and your families all the best and success for the future.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite - preventing SQL injection
PostPosted: Mon Oct 12, 2015 12:35 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Thu Dec 17, 2009 12:00 pm
Posts: 174
Location: Llangadog, Wales, UK
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

_________________
www.deeproot.co.uk


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite - preventing SQL injection
PostPosted: Wed Oct 14, 2015 3:05 pm 
Offline
New User
New User

Joined: Sun Oct 11, 2015 11:13 pm
Posts: 2
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.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 8 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: Exabot [Bot] and 15 guests


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

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye