Whats wrong with this sql statement?

Question

I am using MS ACCESS where I am changing the recordsource of the form in the afterupdate event of a combobox. Here is the sql query used

Me.RecordSource = "SELECT * FROM qryMIMATRIX WHERE qryMIMATRIX.A_LOCATION = " & Me.cboLocate.Value & ";"

cboLocate is the name of the combobox and qryMIMATRIX is the name of the query. On afterupdate event of the I am prompted to enter parameter which is not desirable.Help me out..

I am pretty serious about my SQL Injection comment below. As your query stands you are open to a serious security problem. A combo box by default allows you to enter text manually not just select from a list. This means someone could enter something like.... somevalue; DROP TABLE blah blah.... — Tim Jarvis
– Tim Jarvis, Commented Mar 21, 2012 at 11:04
Setting the combo's "Limit To List" property to Yes prevents injection into the RecordSource SQL statement. However, Access' db engine isn't vulnerable to that DROP TABLE type of injection attack because it will only handle one statement ... you can't join 2 statements with a semicolon and expect the engine to execute both. — HansUp
– HansUp, Commented Mar 21, 2012 at 14:20

Renaud Bompuis · Accepted Answer · 2012-03-23 09:33:23Z

1

If a_loaction field is string type you need to wrap value with quotes:

"SELECT * FROM qryMIMATRIX WHERE qryMIMATRIX.A_LOCATION = '" & Me.cboLocate.Value & "'"

edited Mar 23, 2012 at 9:33

Renaud Bompuis

16.8k4 gold badges59 silver badges86 bronze badges

answered Mar 21, 2012 at 10:43

happyZZR1400

2,4134 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1175126 Over a year ago

Got answers in no time,really.Thanks!I hope I'll be able to figure out things this fast.

user1175126 Over a year ago

This one doesn't seem to be working,I used the same logic:"SELECT * FROM qryMIMATRIX WHERE qryMIMATRIX.A_LOCATION = '" & Me.cboLocate.Value & "' AND qryMIMATRIX.A_PRIORITY= '" & Me.cboPrior.Value &"'" A_PRIORITY is integer,from 1 to 5

Tim Jarvis · Accepted Answer · 2012-03-21 10:47:27Z

1

What's wrong with the query...

Read up on SQL Injection...right now, I'll wait.
...
...
Don't select * ..... not a good practice, specify the columns.
You probably need to quote the value from the combo box

answered Mar 21, 2012 at 10:47

Tim Jarvis

18.9k11 gold badges61 silver badges95 bronze badges

3 Comments

user1175126 Over a year ago

I am sorry I'm ignorant but what is sql injection all about?I think I need to google about this.I will check that out.As of now, I think I can use the LimitToList property to Yes and that will be okay?

Tim Jarvis Over a year ago

Yes, you should read up on it, it's important. Plus, it's easy to fix. There are hundreds (1000's probably) of articles that discuss it. Here is a good one. weblogs.asp.net/scottgu/archive/2006/09/30/…

user1175126 Over a year ago

Thanks.I'm going to check that but what about my query?

Collectives™ on Stack Overflow

Whats wrong with this sql statement?

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related