UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL Injection Attack Prevention, Access 2016    
 
   
SemiAuto40
post Oct 8 2019, 09:20 AM
Post#1



Posts: 704
Joined: 3-April 12
From: L.A. (lower Alabama)


I have a login form which has a combo box and looks up the user name in tbl_Employees by using the combo box value to see if the employee is in the table -- rs.FindFirst "[UserName] = '" & Me.txtUserName.Column(1) & "'" -- and throws an error if an unknown name is used. I thought I could use this for a login purpose... but now I am having second thoughts about doing this on my corporate WAN database application. I've recently run across some information that indicates that it is bad to have a VBA code SQL statement where I refer to the control on the login form (txt_Password) and have something like, WHERE [Password] = '" & txt_Password". That's not exactly it as I am not looking at the code right now but it works. The information indicates that referring to the control Me.txt_Password in the VBA opens me up to SQL injection attacks. Is this true? If it is, can I use a string variable in place of the reference to my form control in the VBA in order to default a vulnerability like this?

Thanks in advance.
Go to the top of the page
 
GroverParkGeorge
post Oct 8 2019, 09:43 AM
Post#2


UA Admin
Posts: 36,043
Joined: 20-June 02
From: Newcastle, WA


SQL Injection is definitely a potential security risk.

The complication here is that you would normally not suspect your users of wanting to implement malicious attacks on your Access Relational Database Application. The exposure is much greater on a public website where ANYONE can pound on a log in until they get in.

Using a parameterized query would be a good idea, yes.
This post has been edited by GroverParkGeorge: Oct 8 2019, 09:44 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
jleach
post Oct 8 2019, 10:47 AM
Post#3


UtterAccess Editor
Posts: 10,218
Joined: 7-December 09
From: St Augustine, FL


>> The information indicates that referring to the control Me.txt_Password in the VBA opens me up to SQL injection attacks. Is this true? If it is, can I use a string variable in place of the reference to my form control in the VBA in order to default a vulnerability like this? <<

It is true, and a string variable won't help you any.

Consider this: User places in the login form: "myname;drop table students"

You run this code with a reference to the control:

CODE
CurrentDb.Execute "SELECT * FROM MyTable WHERE MyName = " & Me.MyControl


The string that gets processed is: "SELECT * FROM MyTable WHERE MyName = myname;drop table students"

That's two SQL Statements. This is SQL injection.

Let's move that to a string variable:

CODE
Dim MyInput As String
MyInput = Me.MyControl
CurrentDb.Execute "SELECT * FROM MyTable WHERE MyName = " & MyInput"


This helps none. The statement that's being processed is identical, including the ";drop table students"

Just putting the value in a string variable won't do anything.

What needs to be done is to sanitize the user input to make sure that it doesn't contain malicious values.

This is where Parameters help considerably. I'm not 100% sure in JET/ACE, but in other systems (SQL Server, ADO.NET, EF, etc), using a parameter shields you from people being able to inject statements this way. It's essentially a built in "sanitize this user input and make sure we only apply it to the appropriate place " function.

Finally, with all that said: certain areas of Access/JET/ACE are implicitly shielded just because of how things operate. DAO, for example, will not execute two statements in a single command (at least I don't THINK so - but don't take it to the bank), and there's other things regarding syntax that prevents some of this from happening. While it may not be quite as easy as I've made out above, it's still very possible. Also note that the example I gave is just one type of SQL injection. OWASP typically has a good rundown on this, though I haven't checked it in a while.


A few additional thoughts:

Generally speaking, any input values should be treated as potentially malicious, whether they come from users putting data in fields, or data being imported from some external source. Nothing is safe until you've made sure it's safe.

Joel Spolsky has a quite interesting article on Hungarian Notation, and uses this scenario as an example. Back when hungarian notation was good, we'd prefix this value with something like "us" as in "unsafe", and if we see a "us" variable, we know it's potentially malicious. Since then though, hungarian notation has turned into prefixing variables with their variable type, which offers no real value. I don't know the article link offhand, but if you happen across it in your travels, it's definitely worth a read.

--------------------
Go to the top of the page
 
jleach
post Oct 8 2019, 10:51 AM
Post#4


UtterAccess Editor
Posts: 10,218
Joined: 7-December 09
From: St Augustine, FL


And of course, the obligatory Little Bobby Tables: https://xkcd.com/327/

--------------------
Go to the top of the page
 
theDBguy
post Oct 8 2019, 11:02 AM
Post#5


Access Wiki and Forums Moderator
Posts: 76,567
Joined: 19-June 07
From: SunnySandyEggo


Wow! I have never considered worrying about SQL Injection in Desktop Applications before, but I guess the concern has merits. I have always only associated SQL Injections with databases connected to public-facing websites.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
SemiAuto40
post Oct 8 2019, 11:05 AM
Post#6



Posts: 704
Joined: 3-April 12
From: L.A. (lower Alabama)


May I trouble you to point me to a parameterized query code block? What I thought were parameterized queries apparently is not. I was using the problem login code as a parameterized query since I supplied the parameter for the query from the form control txt_Password. I saw a reference to an Eval function too but I don't recall if that is for VB.net, Access, or both, as a way to sanitize.

Thank you.
Go to the top of the page
 
theDBguy
post Oct 8 2019, 12:11 PM
Post#7


Access Wiki and Forums Moderator
Posts: 76,567
Joined: 19-June 07
From: SunnySandyEggo


Hi. Here's a couple of links for your reference:

Microsoft

and

OWASP

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Oct 8 2019, 12:44 PM
Post#8


UtterAccess VIP
Posts: 11,689
Joined: 6-December 03
From: Telegraph Hill


>> SELECT * FROM MyTable WHERE MyName = myname;drop table students <<

Have you tried this in Access?

AFAICR, I don't think Access will run this statement anyway since it can't execute multiple statements. I could well be wrong, but haven't tested in a long time.

Also, I don't think comments are legal in Access SQL statements either.

More problematic is user entry like:
CODE
Dim MyInput As String
MyInput = "whatever' OR '1' = '1"
CurrentDb.Execute "SELECT * FROM MyTable WHERE MyName = '" & MyInput & "'"


and potentially revealing all the records in the table.

pullhair.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
kfield7
post Oct 8 2019, 12:53 PM
Post#9



Posts: 1,010
Joined: 12-November 03
From: Iowa Lot


OK so I only work in Access, not SQL Server.
Why not reject logins that contain ";"? or other characters that might be used.
Go to the top of the page
 
jleach
post Oct 8 2019, 03:00 PM
Post#10


UtterAccess Editor
Posts: 10,218
Joined: 7-December 09
From: St Augustine, FL


Hi David:

>> Have you tried this in Access? <<

For the record, I did put a paragraph in there stating that DAO shields us from some of this. I thought it was the case that DAO won't process multiple statements (so your recollection is likely correct, I wasn't 100% sure myself), and there's a few other subtle issues with my "code" in that post, but it wouldn't behoove of me to say "don't worry about it" because someone could well be using ADO or sending it through a passthrough, etc.

It's a simple to understand example of the essence of SQL inject, if not strictly applicable to standard DAO usage that most Access users are going to be using.

Point being: let's never assume we're safe, even if we happen to be in this case wink.gif

Cheers,

--------------------
Go to the top of the page
 
jleach
post Oct 8 2019, 03:08 PM
Post#11


UtterAccess Editor
Posts: 10,218
Joined: 7-December 09
From: St Augustine, FL


I should add:

I think it's very important that people think about this stuff. While I'm not generally one to jump into a forum and throw out something that's actually incorrect, if 10 people look at that and say "hey, maybe I ought to pay closer attention to things" then that's GREAT. Even if it's not a major concern in this particular case, anyone who is doing development work should always have security in their mind at every corner of things.

SQL Injection ought to be part of 101 development. It applies everywhere, to everyone, and in most cases is extremely easy to prevent. Yet it's still a huge (huge!) source of attacks. Injection has been #1 on OWASP's top 10 for many, many years, and it's such an easy*, easy thing to prevent. But people need to know better is all... anything to bring awareness.

(*I say easy, because in every other platform/language except Access/VBA, there is builtin, platform/framework level best practices to avoid it: php has prepared statements, .net has ADO and EF parameters, SQL Server itself should always be done through parameters, etc etc etc - Access/VBA on the other hand has none of that - to be safe against SQL injections is actually much more difficult in here than it is elsewhere: the only saving grace is what George first mentioned about lack of desired attack area, but that's not an excuse, either).

Cheers,

--------------------
Go to the top of the page
 
jleach
post Oct 8 2019, 03:18 PM
Post#12


UtterAccess Editor
Posts: 10,218
Joined: 7-December 09
From: St Augustine, FL


>> Why not reject logins that contain ";"? or other characters that might be used. <<

It's a good thought, but if you do want to be actually secure, it's not something you'd take on yourself. The variations of characters (including non-printables) and other patterns that could force unwanted behavior is no simple list: there's quite a lot to it.

The best way would be to let someone else, with thousands of man-hours or resources (such as Microsoft), handle it instead, and provide tooling.

Access does this with parameterized queries. See here:

https://stackoverflow.com/questions/4950961...icrosoft-access
https://stackoverflow.com/questions/5016600...on-in-ms-access

--------------------
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2019 - 11:28 PM