CodeSOD: The Replacements

Nobody wants to have a Bobby Tables moment in their database. So we need to to sanitize our inputs. Ted C noticed a bunch of stored procedures which contained lines like this:

 @scrubbed = fn_ScrubInput(fn_ScrubInput(@input))

Obviously, they wanted to be super careful, and make sure their inputs were clean. But it got Ted curious, so he checked out how the function was implemented. The function body had one line, the RETURN line, which looked like this:

 RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@input, '"', '"'), '*', '\*'),'~', '\~'), '@', '\@'), '#', '\#'), '$','\$'),'%','\%'),'^','\^'),'&','\&'),'(','\('),')','\)'),'_','\_'),'+','\+'),'=','\='),'>','\>'),'<','\<'),'?','\?'),'/','\/')

Whitespace added.

Ted REPLACE REPLACE REPLACEd this with a call to the built-in STRING_ESCAPE function, which handled the escaping they needed.