I'm trying to pattern match the beginning of a SQL string like:
INSERT INT `rwikiobject` VALUES
('','2006-03-06
23:36:41','/, d
\'','/'
My expression so far looks like:
my ($id, $version, $name, $realm) = $object =~ /INSERT INT
`rwikiobject` VALUES \('([a-z0-9]{32})','(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:
\d{2})','(\/site\/[^']+)','\/site\/([^']+)'/;
This is working great, except for a few cases (like the example string
above where the third field 'name' has an "escaped apostrophe": \'
Do I need a reverse looking assertion to overcome this? If so, I would
really appreciate a hand with the syntax of that. If not, any ideas how
I can match this field: which is of unknown length, and can contain any
character / symbol, including the escaped form of the apostrophe, which
is also the field delimiter.
Thanks in advance,
Paul

Paul Beckett wrote:
>
I'm trying to pattern match the beginning of a SQL string like:
INSERT INT `rwikiobject` VALUES
('','2006-03-06
23:36:41','/, d
\'','/'
>
My expression so far looks like:
my ($id, $version, $name, $realm) = $object =~ /INSERT INT
`rwikiobject` VALUES \('([a-z0-9]{32})','(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:
\d{2})','(\/site\/[^']+)','\/site\/([^']+)'/;
>
This is working great, except for a few cases (like the example string
above where the third field 'name' has an "escaped apostrophe": \'
>
Do I need a reverse looking assertion to overcome this? If so, I would
really appreciate a hand with the syntax of that. If not, any ideas how
I can match this field: which is of unknown length, and can contain any
character / symbol, including the escaped form of the apostrophe, which
is also the field delimiter.

Hi Paul.

Those string values look exactly like Perl syntax to me, so I would forget about
regexes and treat it as Perl source. Imagine if you replace all that stuff up
to the values list with 'my @values = ', that would give you what you wanted
wouldn't it? The code below grabs the contents of the SQL string between (and
including) the first '(' and the last ')'. It then calls eval() to process it as
Perl source and assign the values to the array. Note that the escaping backslash
has been removed from the single quote. I hope this works for you.

Rob

use strict;
use warnings;

my $sql = q[INSERT INT `rwikiobject` VALUES
('','2006-03-06
23:36:41','/, d
\'','/');];

$sql =~ /(\(.*\))/;

my ($id, $version, $name, $realm) = eval $1;

print "$_\n" foreach ($id, $version, $name, $realm);

UTPUT

2006-03-06 23:36:41
/, d '
/

8/4/06, Rob Dixon <rob.dixon (AT) 350 (DOT) comwrote:

The code below grabs the contents of the SQL string between (and
including) the first '(' and the last ')'. It then calls eval() to process it

Please don't use the evil eval for this. You're using a flamethrower
to light candles. Slip the string 'unlink <*>' into the database, and
you'll be sad.

Cheers!

Phoenix
Stonehenge Perl Training

Tom,
My data is coming from a trusted source (dumped from one of my databases).
Presumably even if someone put something evil like unlink into a field in my database, mysqldump will have escaped any apostophes etc, so I guess in this specific situation it should be safe.
Thanks for the warning though - definately a command to be very careful with.
Many thanks to Rob as well, the eval solution works nicely.
Cheers,
Paul

Message
From: tom.phoenix (AT) gmail (DOT) com on behalf of Tom Phoenix
Sent: Fri 8/4/2006 4:51 PM
To: Rob Dixon
Cc: beginners (AT) perl (DOT) org
Subject: Re: reg-ex match - reverse looking assertion?

8/4/06, Rob Dixon <rob.dixon (AT) 350 (DOT) comwrote:

The code below grabs the contents of the SQL string between (and
including) the first '(' and the last ')'. It then calls eval() to process it

Please don't use the evil eval for this. You're using a flamethrower
to light candles. Slip the string 'unlink <*>' into the database, and
you'll be sad.

Cheers!

Phoenix
Stonehenge Perl Training

08/04/2006 04:26 AM, Paul Beckett wrote:
I'm trying to pattern match the beginning of a SQL string like:
INSERT INT `rwikiobject` VALUES
('','2006-03-06
23:36:41','/, d
\'','/'

My expression so far looks like:
my ($id, $version, $name, $realm) = $object =~ /INSERT INT
`rwikiobject` VALUES \('([a-z0-9]{32})','(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:
\d{2})','(\/site\/[^']+)','\/site\/([^']+)'/;

This is working great, except for a few cases (like the example string
above where the third field 'name' has an "escaped apostrophe": \'

Do I need a reverse looking assertion to overcome this? If so, I would
really appreciate a hand with the syntax of that. If not, any ideas how
I can match this field: which is of unknown length, and can contain any
character / symbol, including the escaped form of the apostrophe, which
is also the field delimiter.

Thanks in advance,
Paul

Use Text::Balanced. The extract_delimited function will be the
most useful.

Fri, 04 Aug 2006 10:26:16 +0100, Paul Beckett wrote:
I'm trying to pattern match the beginning of a SQL string like:
INSERT INT `rwikiobject` VALUES
('','2006-03-06
23:36:41','/, d
\'','/'

Wouldn't your life be a lot easier, not to mention reduced in regex
complexity, if you used SQL::Parser from CPAN?