1

I'm working on a SQL query that pulls information from a database into Excel. However, the source is from a website so I can't control what characters they use. I don't have the ability to edit the fields in the table.

I've run into an issue where the site uses the ' character sometimes in a field, which causes issues in my code:

Example Field: "Something's Here"

WHERE (Table.fieldName='Something's Here')

The ' causes an error, but I know I can just change the ' to '' through VBA in this case.

Are there any other characters that can cause issues? Perhaps ( or ) or - or ,

EDIT: Please note that I do not know PHP, and don't really understand what a prepared statement is or if that would work in my case. To reiterate, I am using Excel to import data via an ODBC connection. These are stored on Tables in Excel. The user inputs a number, which then updates the ODBC connections via VBA. There is a related field (that the user does not enter) based off the number the user entered that sometimes has characters that aren't allowed. I don't know that a prepared statement will be able to fix that.

Community
  • 1
  • 1
M G
  • 11
  • 3
  • 4
    Use prepared statements instead, and you won't have to worry about this. – Siyual Aug 29 '17 at 17:50
  • 1
    Beyond worrying about what characters you should be concerned with, this whole set up sounds like a giant opening for a SQL Injection Attack. As @Siyual mentions, you should use a prepared statement and bind your parameters to avoid this issue (it will fix the character issue as well). Check out [this decent example](https://www.mcpressonline.com/programming-other/microsoft/techtip-use-prepared-statements-with-ado-to-access-iseries-data) if you are using ADODB to see how to use parameters when building your query. – JNevill Aug 29 '17 at 17:54
  • 1
    Possible duplicate of [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Tab Alleman Aug 29 '17 at 18:07

1 Answers1

1

You have run into the age old problem of having to "Scrub" your data. This happens often when you have operators or outside places pulling things off the web. The problem being, you can't control what others put into your system.

As you mentioned, a "Single Quote" can cause a problem. Or maybe a comma can cause problems. Also, other invisible ascii characters can cause problems. (Especially if you are sending text to a printer.) Something like a Line Feed Carriage Return can cause all kinds of problems and you don't even see that they are there.

So to answer your question of "what other characters to be careful of...", I'd rephrase that into "What characters do I want to allow".

Here is a link to a map of all ascii characters where you can use VBA to scan each string you read in.

This is time consuming, but something necessary if you need to scrub your string data.

The basic idea is to parse through the string you are reading in (or are going to read in) and on a character by character basis check against logic to see if the ascii code is in an acceptable range.

ie:

Dim strChar As String
Dim intAsc As Integer
Dim booFlag As Boolean

intAsc = Asc(strChar)
If ((intAsc >= 65) And (intAsc <= 90)) _
   Or ((intAsc >= 97) And (intAsc <= 122)) _
   Or ((intAsc >= 48) And (intAsc <= 57)) _
   Or ((intAsc = 32) _
Then
   booFlag = True
End If

Where the range 65 - 90 is your Uppercase Letters. 97 - 122 is Lowercase. 48 - 57 is Zero through Nine. And 32 is ascii for a true Space character. And you can add other "Or" statements to allow only codes you want in your database. However, you'd want to leave out the ascii for the "Single Quote" and other invisible stuff.

You can put that code in a VBA function and then parse through each string you want to check. If you find something not in the approved list, you can change it to a space character (or something else).

Also, if you are worried about SQL injection, instead of scanning for keywords, you should consider using a parameterized query to send your SQL command.

Hope that gives you a framework to work with. :)

abraxascarab
  • 661
  • 1
  • 6
  • 13
  • Thank you very much for this. This is more what I was looking for. Based on my data source, I am not very worried about SQL Injection. I was hoping someone here would be able to point me in the direction of what characters cause an issue, but I may have to do that manually. – M G Aug 29 '17 at 19:49
  • Yes... it depends on how you are building your SQL and what you are using it for. If it's just comparing to a varchar, a single quote and a null terminator are real problems (plus invisible control characters). In a `Like` statement, you have % and other wild card chars to worry about. If you are using an `IN` statement, you have to worry about how commas are placed in your data. So there are a bunch of different answers based on what you are trying to accomplish. – abraxascarab Aug 29 '17 at 20:38