CODEWITHSUNDEEP
sql-serverregexvbscript
user736893
user736893

Reputation:

Regex for safe SQL insert

I have a vbscript that inserts some strings into a database. Often, these strings have weird characters, quotes, apostrophes, ampersands, etc. I am currently using the following string to replace all but certain characters, but it's replacing semicolons and some other stuff (including spaces) that I would ideally like to keep. So I'm basically looking for the least restrictive regex that will still generate a sql safe string.

For what it's worth, the strings are Windows installed applications (as you would see in Add/Remove Programs).

Function CleanUp (input)
  Dim objRegExp, outputStr
  Set objRegExp = New Regexp

  objRegExp.IgnoreCase = True
  objRegExp.Global = True
  objRegExp.Pattern = "((?![a-zA-Z0-9]).)+"
  outputStr = objRegExp.Replace(input, "-")

  objRegExp.Pattern = "\-+"
  outputStr = objRegExp.Replace(outputStr, "-")

  CleanUp = outputStr
End Function

Upvotes: 0

Views: 1805

Answers (2)

user736893
user736893

Reputation:

This seems to work great so far in my testing:

objRegExp.Pattern = "[^\x20-\x26,^\x28-\x7E]" 'Space through &, skips ', then ( through ~

I didn't know you could specify a range of ascii values. This covers everything between Space and ~, excluding a single quote (').

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294317

Why does the string have to be SQL safe? Use parameters and be done with it. Hack-Proofing Your Applications

Upvotes: 2

Related Questions