Hi
I would like a regex that would make this:
VALUES('Hit 'n Run')
into
VALUES('Hit ''n Run')
Is this possible?
views:
96answers:
2
+4
A:
No, this is not really possible. If you have VALUES('Hit 'n Run'), you already have an invalid mixture of delimiting apostrophes and literal apostrophes. String processing is like mixing sugar and salt: once you've mixed contexts without proper escaping there is no way of pulling them back apart.
If you are trying to rescue broken data, you could try something like (?<!\()'(?!\)) to match apostrophes that don't have a bracket next to them. It's a weak and easily fooled tactic but for simple data it might work.
If you are putting together dynamic SQL queries you must escape the ' before you put it into the query string, either using a simple string replace ' with '' if you're sure that's the only escape your DBMS requires, or — much better — using a dedicated SQL-string-literal-escaping function appropriate to your DBMS. Quite what that function would be depends on what platform (language, DBMS) you're talking about.
bobince
2010-01-02 15:44:17
I figured, but i thought i would check first. I have a lot of sql string that aren't escaped and all are sent to a "execute" method. So i figured i could save time by just escaping the parameter. Well, nothing to do then.
Erik
2010-01-02 15:46:44
Or *a lot more* to do depending on how you look at it. I'm not sure what language/framework you're using but refactoring that code to use parameterized queries may be worth the effort.
Josh Einstein
2010-01-02 15:50:03
The methods that builds the query takes an object as a parameter and extracts data from that. By nothing to do then, i actually meant nothing to do but to escape every single method.
Erik
2010-01-02 15:53:21
+1 for parameterised queries. If available in your environment, they're likely to be more pleasant to use than many calls to string escape.
bobince
2010-01-02 16:11:26
+2
A:
Any pattern that could be expressed in RegEx could then be exploited to create the very SQL injection issues you're trying to avoid.
Example nasty input:
VALUES(');DELETE * FROM customer;SELECT '
richardtallent
2010-01-02 15:47:17
Actually, i'm not avoiding that. It is a private application for home use. So the only one one would sabotage by doing that it oneself.
Erik
2010-01-02 15:50:18
*The Bicentennial Man* was a good movie, but you can use 'I' and 'me' online, really. :)
Roger Pate
2010-01-02 16:05:27