Solved

Regex for a SQL INSERT statement

Posted on 2006-11-07
9
196 Views
Last Modified: 2008-02-26
I am trying to write a regex to validate an insert statement. I am writing it in pieces. The part I am having trouble with is the VALUES area of the sql statement. The problem is that any value will either be enclosed in single quotes or may be NULL. I am having trouble getting the syntax correct on this one. Can someone assist in this? Thanks.
0
Comment
Question by:rbichon
  • 5
  • 3
9 Comments
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 17894220
The fact that quotes can be escaped inside quotes makes this a little tricky, but I can mentally construct a Deterministic Finite Automata that does it, so can therefore prove that there exists a regular expression to do it.  (Hey, who says all those theory classes in grad school were for naught?)

I think the basic structure for each value is gonna have to be:

(a_value_in_quotes | an_expression | NULL)


To handle a value in quotes I'm thinking this will do the trick:
' (\.|[^'\])* '

That is, between two quotes you can have either any character preceded by a backslash, or any character other than a quote.

It's the "expression" part that gets really tricky to the point that I'm pretty sure expressions (even in SQL) are context free, not regular.  If you're forbidding expressions then you're probably set.
0
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 17894260
Heh, I forgot to put the "a value" regexp together into "a list of values".

I can't think of a way to write this without repeating the regexp twice, but... it'll have to do.  (-:

\(
    ( ' ( \.  |   [^'\] )* ' | NULL)
    (
        ,[\s]*
        (' ( \.  |   [^'\] )* ' | NULL)
    )*
\)

That should get a list of values separated by commas, yet forbidding an extra comma at the beginning or end.
0
 
LVL 1

Author Comment

by:rbichon
ID: 17894962
That is true. The regex has to be repeated because of the comma. I think you have the '\ backwards though. Shouldn't it be \'? Otherwise you are escaping the bracket which will cause an error. I will test this at work tomorrow. Thanks for your help.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 250 total points
ID: 17895005
Yeah, some of my backslashes are meant to escape regexp characters and some are supposed to be literal backslashes.  Do you mean the parser can't tell the difference on its own?  (-;

Let's try that again:

\(
    ( ' ( \\.  |   [^'\\] )* ' | NULL)
    (
        ,[\s]*
        (' ( \\.  |   [^'\\] )* ' | NULL)
    )*
\)
0
 
LVL 1

Author Comment

by:rbichon
ID: 18084746
Your answer was close in theory but lacks a certain level of experience. Any space or line break you put in a regular expression is usually interpreted as a part of the expression.
0
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 18084793
Sure, you can either set the parser to ignore whitespace in the pattern, which means you can keep the more human-readable format, or you can (as I expected you would) remove the whitespace when actually coding it, on the (probably valid) assumption that nobody reading it in the future is gonna understand it without mentally parsing it anyway.

Suggesting I lack experience because I sought to produce a more legible version of the code is rude, incidentally; there's no need to insult me or my intelligence.
0
 
LVL 1

Author Comment

by:rbichon
ID: 18085223
I don't mean to insult but there are several obvious syntax errors in the code. And I might be wrong but I have never seen a way to configure the parser to accept the expression in the form that you have it. Long story short, your solution did not work on many levels.
0
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 18085414
I see.  I tested with a PCRE parser, where that syntax is perfectly valid; I know Microsoft imposes some odd rules on its regexp engine in .NET but I didn't realize it would hiccup on something so straightforward.

Sorry I couldn't be of more direct help.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question