regexp experts, need some help

[Rhugga]

My perl is rusty and my regexps even rustier. I want to search and
replace a certain string from log files that I process and store into
oracle. Here is a sample entry:

Feb 14 11:14:05 db-0201 su: [ID 366847 auth.info] 'su oracle' succeeded
for root on /dev/???

The field I want to remove is [ID 366847 auth.info]

I don't know how large the ID number can be so I will just assume 2^64
and the auth.info part is standard syslog facility.priority.

Thanks for any help.
cc

 

[phaylon]

The field I want to remove is [ID 366847 auth.info]

Hm. s/\[.*?\]//g; or you haven't said something. Is this the only
point where the [ .. ] will come up in this log-messages?

 

[phaylon]

One can specify this in as much detail as desired:

Yes. but there's also, like almost everywhere, a too much.

 

[Josef Moellers]

Rhugga wrote:

The field I want to remove is [ID 366847 auth.info]

Hm. s/\[.*?\]//g; or you haven't said something. Is this the only
point where the [ .. ] will come up in this log-messages?

One can specify this in as much detail as desired:

s/\[ID\s\d+\s\w+\.\w+\]//

Josef

 

[Rhugga]

Yea, that is the only place it will appear and can only appear once. I
figured it it was something simple just haven't done regexp in 5 years,
heh.

Thx,
cc

 

[phaylon]

Yea, that is the only place it will appear and can only appear once. I
figured it it was something simple just haven't done regexp in 5 years,
heh.

Then go for it, you'll love it

 

[phaylon]

$log_message =~ s/'/\'/g;

Double the \, else you are escaping ' for the regex:

phaylon@hamlett:~/ptests> perl
$a = "test ' abc";
$a =~ s/'/\\'/g;
print "$a\n";^D
test \' abc
phaylon@hamlett:~/ptests>

hth,phay

 

[Rhugga]

Ok, down to one more roadblock. I am parsing the following sample log
entry and tokenizing it into fields and then shoving into oracle. The
problem is that some log messages will contain ' symbols. I am trying
to escape these so I can insert into oracle:

Here is a sample log message:
Feb 15 07:49:55 db-0201 su auth info su: [ID 366847 auth.info] 'su
oracle' succeeded for root on /dev/???

Here is the code where I'm trying to replace ' with \':

$log_message =~ s/\[.*?\]//g;
$log_message =~ s/ +/ /g;
$log_message =~ s/^ +//g;
$log_message =~ s/'/\'/g;
if ($DEBUG > 1) { print STDOUT "DEBUG: $log_message\n"; }

However, this is not working. here is a resulting query built using
$log_message:
DEBUG: $query = INSERT INTO SYSADMIN.SYSLOG VALUES (
SYSLOG_PKEY_SEQ.NEXTVAL, to_date('2005-Feb-15 07:44:54', 'YYYY-MON-DD
HH24:MI:SS'), '2', 'db-0201', 'su', 'auth', 'info', 'su: 'su oracle'
succeeded for root on /dev/???' )

Oracle is interpresting 'su: 'su oracle' succeeded for root on
/dev/???' as 2 fields and I need it to treat this as one field.

One option is to simply remove the ' or replace them with " or
something else but I really desire to keep the log mesage completely
intact with the exception of extra white space removed.

 

[A. Sinan Unur]

I am trying to escape these so I can insert into oracle:

Here is a sample log message:
Feb 15 07:49:55 db-0201 su auth info su: [ID 366847 auth.info] 'su
oracle' succeeded for root on /dev/???

Here is the code where I'm trying to replace ' with \':

$log_message =~ s/\[.*?\]//g;
$log_message =~ s/ +/ /g;
$log_message =~ s/^ +//g;
$log_message =~ s/'/\'/g;
if ($DEBUG > 1) { print STDOUT "DEBUG: $log_message\n"; }

I don't know why this is necessary.

However, this is not working. here is a resulting query built using
$log_message:
DEBUG: $query = INSERT INTO SYSADMIN.SYSLOG VALUES (
SYSLOG_PKEY_SEQ.NEXTVAL, to_date('2005-Feb-15 07:44:54', 'YYYY-MON-DD
HH24:MI:SS'), '2', 'db-0201', 'su', 'auth', 'info', 'su: 'su oracle'
succeeded for root on /dev/???' )

Are you using DBI?

Oracle is interpresting 'su: 'su oracle' succeeded for root on
/dev/???' as 2 fields and I need it to treat this as one field.

Then you should quote it properly. Blind substitution is not going to
help you do it properly.

One option is to simply remove the ' or replace them with " or
something else but I really desire to keep the log mesage completely
intact with the exception of extra white space removed.

You might want to look at the DBI documentation:

quote

$sql = $dbh->quote($value);
$sql = $dbh->quote($value, $data_type);

Quote a string literal for use as a literal value in an SQL
statement, by escaping any special characters (such as quotation marks)
contained within the string and adding the required type of outer
quotation marks.

Sinan.

 

[Rhugga]

Made a mistake in that code,

$log_message =~ s/'/\'/g;

should be

$log_message =~ s/'/\\'/g;

Which then gives me the resulting query:
DEBUG: $query = INSERT INTO SYSADMIN.SYSLOG VALUES (
SYSLOG_PKEY_SEQ.NEXTVAL, to_date('2005-Feb-15 07:44:54', 'YYYY-MON-DD
HH24:MI:SS'), '4', 'db-0201', 'su', 'auth', 'info', 'su: \'su oracle\'
succeeded for root on /dev/???' )

So I think my problem is oracle related now and figuring out how to
insert ' into an oracle varchar2 column. Am I correct in this?

Thx,
cc

 

[Josef Moellers]

Josef Moellers wrote:




Yes. but there's also, like almost everywhere, a too much.

I prefer to specify anything that is fixed, to adhere to the principle
of least surprise. You never know what's lurking behind the corner.
OTOH, this can backfire, I agree.