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?

Erik A
  • 31,639
  • 12
  • 42
  • 67
Isaac Reefman
  • 537
  • 1
  • 8
  • 26

2 Answers2

5

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

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

Set qdf = db.CreateQueryDef(vbNullString, strSql)
With qdf
    .Parameters("str_name").Value = strName
    .Parameters("str_phone").Value = strPhone
    .Parameters("str_address").Value = strAddress
    .Execute dbFailOnError
End With
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 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? – Isaac Reefman May 04 '18 at 02:12
  • 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. – Isaac Reefman May 04 '18 at 02:13
  • 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). – Tim Biegeleisen May 04 '18 at 02:15
  • 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 – Isaac Reefman May 04 '18 at 02:20
  • 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? – Isaac Reefman May 04 '18 at 03:06
  • I don't know enough about this API to answer; I based my answer on other documentation. From what I can see, dbFailOnError is just a like a catch block in most other languages. – Tim – Tim Biegeleisen May 04 '18 at 03:27
  • Sweet. All g. I've been doing more research as I go, and I'm suspecting more and more that my guesses were right. Thanks for getting me this far! – Isaac Reefman May 04 '18 at 03:29
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.

Eric Yang
  • 2,678
  • 1
  • 12
  • 18