2

I'm trying to better understand parameterized sql as a solution to SQL injection.

Lets say I have a tblCustomer with the fields CustName, Phone and Address. Lets also say I have an input form for new customers to enter their data, with controls called txtName, txtPhone and txtAddress.

I could run the following vba code:

dim strName, strPhone strAddress, strSQL as string
strName = me.txtName
strPhone = me.txtPhone
strAddress = me.txtAddress
strSQL = "INSERT INTO tblCustomer (CustName, Phone, Address) _
VALUES (" & strName & ", " & strPhone & ", " & strAddress & ");"

DoCmd.RunSQL strSQL

But then if someone nominated the address "Robert'); DROP TABLE tblCustomer; --" (wink) I'd have some serious problems.

I've used vba parameters, but they aren't helping me. So when people say use parameters to fix the issue, what do they mean?

Improve this question
0

2 Answers 2

Reset to default
5

Using a prepared statement with positional parameters eliminates the chance that someone may SQL inject you:

Dim strSQL as String
Dim qdf as QueryDef

strSQL = "INSERT INTO tblCustomer (CustName, Phone, Address) " &
    "VALUES ([str_name], [str_phone], [str_address]);"

Set qdf = CurrentDb.CreateQueryDef(vbNullString, strSql)
With qdf
    .Parameters("str_name").Value = strName
    .Parameters("str_phone").Value = strPhone
    .Parameters("str_address").Value = strAddress
    .Execute dbFailOnError
End With
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

This looks like exactly what I'm looking for, and/but I have questions: 1. Why didn't you have to define "qdf"? if you don't, is it automatically a variant? Do we need it to be a variant so it simply executes what we said within vba, for the sole purpose of saving us typing/space? Is this not allowed if you're working in Option Explicit? 2. Why vbNullString as the QueryDef name? Is this to avoid actually creating and recording a new QueryDef object each time we define a parameter for the query? 3. Why wouldn't you run a PARAMETERS statement along with the INSERT one?
Wow. I thought there were going to be some line breaks in there. Sorry. Looks like a mess and a breathless rant full of demands.
I'm not a VBA person, but I know enough about statements to offer at least a step in the right direction for you. Whitespace should not matter, provided that you separate keywords and data properly in your query string (i.e. don't use SELECTcolFROMmyTable, because that obviously won't run).
Not sure what you're referring to re whitespace... My comment about line breaks was referring to my own comment above, in case that was a source of confusion. Trust me, I've only been working in VBA for about 6 weeks. Compared to me you're definitely the mvp of vba... :P
K. Sat down with a mate and worked out most of what you meant in your answer. Can you confirm a couple of things though: did you use With qdf, rather than just qdf.Parameters(... each time just to save you writing qdf multiple times? And is .Execute dbFailOnError an option for execute where it undoes any changes to the data if it encounters an error along the way?
|
0

See here:

https://msdn.microsoft.com/en-us/library/office/ff845220.aspx

A longer winded explanation is that a parameterized query allows you to use a variable within your SQL query, and that variable will be properly escaped if it's a string or matched to your data type (for type checking), and prevent the problem that you've listed.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.