How to escape single quotes inside fields but not the ones aroundfields?

H

Henry J.

Need to escape single quotes ( i.e., ' -> '' ) in a data file before
sending to DB as part of insert SQLs.

Example 1):

it's mine, it's yours, 12, 42, 2008/10/06 => it''s mine,
it''s yours, 12, 42, 2008/10/06

Example 2):

'it's mine', 'it's yours', 12, 42, '2008/10/06' => 'it''s
mine', 'it''s yours', 12, 42, '2008/10/06'

The tricky part is that the data file may or may not have the string
fields wrapped in single quotes. In Example 1), it is not and single
quotes around fields will be added by another script before sending to
DB, in Example 2), its fields are already enclosed in single quotes
and will be sent to DB as is.

Does anybody have handy perl one-liner or script that tackles this?
Thanks!
 
R

RedGrittyBrick

Henry said:
Need to escape single quotes ( i.e., ' -> '' ) in a data file before
sending to DB as part of insert SQLs.

Example 1):

it's mine, it's yours, 12, 42, 2008/10/06 => it''s mine,
it''s yours, 12, 42, 2008/10/06

Example 2):

'it's mine', 'it's yours', 12, 42, '2008/10/06' => 'it''s
mine', 'it''s yours', 12, 42, '2008/10/06'

The tricky part is that the data file may or may not have the string
fields wrapped in single quotes. In Example 1), it is not and single
quotes around fields will be added by another script before sending to
DB, in Example 2), its fields are already enclosed in single quotes
and will be sent to DB as is.

Does anybody have handy perl one-liner or script that tackles this?
Thanks!

$dbh->prepare("SELECT foo, bar FROM table WHERE baz=?")->execute($baz);
 
S

sln

Need to escape single quotes ( i.e., ' -> '' ) in a data file before
sending to DB as part of insert SQLs.

Example 1):

it's mine, it's yours, 12, 42, 2008/10/06 => it''s mine,
it''s yours, 12, 42, 2008/10/06

Example 2):

'it's mine', 'it's yours', 12, 42, '2008/10/06' => 'it''s
mine', 'it''s yours', 12, 42, '2008/10/06'

The tricky part is that the data file may or may not have the string
fields wrapped in single quotes. In Example 1), it is not and single
quotes around fields will be added by another script before sending to
DB, in Example 2), its fields are already enclosed in single quotes
and will be sent to DB as is.

Does anybody have handy perl one-liner or script that tackles this?
Thanks!


I don't think this can be a one liner, maybe.
In esscence this does it all:

sln

#############
# Delim Regex
#############

use strict;
use warnings;

my $str = "
it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12, 42, '2008/10/06',
it's wants it's gets ";


while ($str =~ /([^,\n]+)/g)
{
my $val = $1;
$val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;
$val =~ s/'/''/g;
$val = "'$val'";
print "val = $val\n";
}


__END__

output:

val_a = 'it''s mine'
val_a = 'it''s yours'
val_a = '12'
val_a = '42'
val_a = '2008/10/06'
val_a = 'it''s mine'
val_a = 'it''s yours'
val_a = '12'
val_a = '42'
val_a = '2008/10/06'
val_a = 'it''s wants it''s gets'
 
S

sln

Need to escape single quotes ( i.e., ' -> '' ) in a data file before
sending to DB as part of insert SQLs.

Example 1):

it's mine, it's yours, 12, 42, 2008/10/06 => it''s mine,
it''s yours, 12, 42, 2008/10/06

Example 2):

'it's mine', 'it's yours', 12, 42, '2008/10/06' => 'it''s
mine', 'it''s yours', 12, 42, '2008/10/06'

The tricky part is that the data file may or may not have the string
fields wrapped in single quotes. In Example 1), it is not and single
quotes around fields will be added by another script before sending to
DB, in Example 2), its fields are already enclosed in single quotes
and will be sent to DB as is.

Does anybody have handy perl one-liner or script that tackles this?
Thanks!


I don't think this can be a one liner, maybe.
In esscence this does it all:

sln

#############
# Delim Regex
#############

use strict;
use warnings;

my $str = "
it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12, 42, '2008/10/06',
it's wants it's gets ";


while ($str =~ /([^,\n]+)/g)
{
my $val = $1;
$val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;

# just for grins, reconstitute partial fixes.
# there goes that one-liner possibility

$val =~ s/'+/'/g;
#
 
C

Charlton Wilbur

HJ> Need to escape single quotes ( i.e., ' -> '' ) in a data file
HJ> before sending to DB as part of insert SQLs.

Use placeholders in your SQL statement, and let the database driver
handle escaping as necessary. perldoc DBI

Charlton
 
H

Henry J.

Need to escape single quotes ( i.e., ' -> '' ) in a data file before
sending to DB as part of insert SQLs.
      Example 1):
          it's mine, it's yours, 12, 42, 2008/10/06   => it''s mine,
it''s yours, 12, 42, 2008/10/06
      Example 2):
          'it's mine', 'it's yours', 12, 42, '2008/10/06'  => 'it''s
mine', 'it''s yours', 12, 42, '2008/10/06'
The tricky part is that the data file may or may not have the string
fields wrapped in single quotes.  In Example 1), it is not and single
quotes around fields will be added by another script before sending to
DB, in Example 2), its fields are already enclosed in single quotes
and will be sent to DB as is.
Does anybody have handy perl one-liner or script that tackles this?
Thanks!

I don't think this can be a one liner, maybe.
In esscence this does it all:

sln

#############
# Delim Regex
#############

use strict;
use warnings;

my $str = "
it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12,42, '2008/10/06',
it's wants it's gets ";

while ($str =~ /([^,\n]+)/g)
{
        my $val = $1;
        $val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;
        $val =~ s/'/''/g;
        $val = "'$val'";
        print "val = $val\n";

}

__END__

output:

val_a = 'it''s mine'
val_a = 'it''s yours'
val_a = '12'
val_a = '42'
val_a = '2008/10/06'
val_a = 'it''s mine'
val_a = 'it''s yours'
val_a = '12'
val_a = '42'
val_a = '2008/10/06'
val_a = 'it''s wants it''s gets'- Hide quoted text -

- Show quoted text -

Thanks a lot! I'm able to adapt it a bit to solve my problem (e.g., I
cannot add or remove enclosing single quotes).
 
S

sln

Need to escape single quotes ( i.e., ' -> '' ) in a data file before
sending to DB as part of insert SQLs.
      Example 1):
          it's mine, it's yours, 12, 42, 2008/10/06   => it''s mine,
it''s yours, 12, 42, 2008/10/06
      Example 2):
          'it's mine', 'it's yours', 12, 42, '2008/10/06'  => 'it''s
mine', 'it''s yours', 12, 42, '2008/10/06'
The tricky part is that the data file may or may not have the string
fields wrapped in single quotes.  In Example 1), it is not and single
quotes around fields will be added by another script before sending to
DB, in Example 2), its fields are already enclosed in single quotes
and will be sent to DB as is.
Does anybody have handy perl one-liner or script that tackles this?
Thanks!

I don't think this can be a one liner, maybe.
In esscence this does it all:

sln

#############
# Delim Regex
#############

use strict;
use warnings;

my $str = "
it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12, 42, '2008/10/06',
it's wants it's gets ";

while ($str =~ /([^,\n]+)/g)
{
        my $val = $1;
        $val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;
        $val =~ s/'/''/g;
        $val = "'$val'";
        print "val = $val\n";

}

__END__

output:

val_a = 'it''s mine'
val_a = 'it''s yours'
val_a = '12'
val_a = '42'
val_a = '2008/10/06'
val_a = 'it''s mine'
val_a = 'it''s yours'
val_a = '12'
val_a = '42'
val_a = '2008/10/06'
val_a = 'it''s wants it''s gets'- Hide quoted text -

- Show quoted text -

Thanks a lot! I'm able to adapt it a bit to solve my problem (e.g., I
cannot add or remove enclosing single quotes).

Without at least removeing enclosing quotes, the inner quote would be hard to
substitute. Not impossible, but harder. Its easy to test if there is enclosing
quote (then add it back later), but it still has to be removed before doing the
inner quote substitution.

Either way it can be done. How did you do it?

Thanks!

sln
 
H

Henry J.

Need to escape single quotes ( i.e., ' -> '' ) in a data file before
sending to DB as part of insert SQLs.
      Example 1):
          it's mine, it's yours, 12, 42, 2008/10/06   => it''s mine,
it''s yours, 12, 42, 2008/10/06
      Example 2):
          'it's mine', 'it's yours', 12, 42, '2008/10/06'  => 'it''s
mine', 'it''s yours', 12, 42, '2008/10/06'
The tricky part is that the data file may or may not have the string
fields wrapped in single quotes.  In Example 1), it is not and single
quotes around fields will be added by another script before sending to
DB, in Example 2), its fields are already enclosed in single quotes
and will be sent to DB as is.
Does anybody have handy perl one-liner or script that tackles this?
Thanks!
I don't think this can be a one liner, maybe.
In esscence this does it all:
sln
#############
# Delim Regex
#############
use strict;
use warnings;
my $str = "
it's mine, it's yours, 12, 42, 2008/10/06, 'it's mine', 'it's yours', 12, 42, '2008/10/06',
it's wants it's gets ";
while ($str =~ /([^,\n]+)/g)
{
        my $val = $1;
        $val =~ s/^[\s'"]+//; $val =~ s/[\s'"]+$//;
        $val =~ s/'/''/g;
        $val = "'$val'";
        print "val = $val\n";
}
__END__
output:
val_a = 'it''s mine'
val_a = 'it''s yours'
val_a = '12'
val_a = '42'
val_a = '2008/10/06'
val_a = 'it''s mine'
val_a = 'it''s yours'
val_a = '12'
val_a = '42'
val_a = '2008/10/06'
val_a = 'it''s wants it''s gets'- Hide quoted text -
- Show quoted text -
Thanks a lot!  I'm able to adapt it a bit to solve my problem (e.g., I
cannot add or remove enclosing single quotes).

Without at least removeing enclosing quotes, the inner quote would be hard to
substitute. Not impossible, but harder. Its easy to test if there is enclosing
quote (then add it back later), but it still has to be removed before doing the
inner quote substitution.

Either way it can be done. How did you do it?

Thanks!

sln- Hide quoted text -

- Show quoted text -

I replace it with a string and then replace it back.

BTW, i cannot use the placeholders to handle strings because this is a
generic function in a perl lib that works like bcp in sybase (i.e.,
loading a given data file into any given table in DB).
 
S

sln

I replace it with a string and then replace it back.

BTW, i cannot use the placeholders to handle strings because this is a
generic function in a perl lib that works like bcp in sybase (i.e.,
loading a given data file into any given table in DB).

Ok, workarounds are acceptable.. good luck.
Btw, I used to do alot of ADO back in the day. In need of a job if you know of any.

sln
 
H

Henry J.

Ok, workarounds are acceptable.. good luck.
Btw, I used to do alot of ADO back in the day. In need of a job if you know of any.

sln

Sorry I'm in NYC and you know how rough it is around here. Good luck.
 
P

Peter Scott

BTW, i cannot use the placeholders to handle strings because this is a
generic function in a perl lib that works like bcp in sybase (i.e.,
loading a given data file into any given table in DB).

I'm unconvinced. Sure, you can't use placeholders for table names or
column names, but you don't typically get apostrophes in those. At some
point you are generating an INSERT statement with a set of values and I
do not see why you can't build something that uses placeholders, unless
this can't use DBI at all.
 
H

Henry J.

I'm unconvinced.  Sure, you can't use placeholders for table names or
column names, but you don't typically get apostrophes in those.  At some
point you are generating an INSERT statement with a set of values and I
do not see why you can't build something that uses placeholders, unless
this can't use DBI at all.

What I'm trying here is to fix a problem in the legacy code. Perhaps
I should consider writing a new function to do what you describe and
ask everybody to use it going forward. Thanks.
 
H

Henry J.

What I'm trying here is to fix a problem in the legacy code.  Perhaps
I should consider writing a new function to do what you describe and
ask everybody to use it going forward.  Thanks.

OK, one problem pops up trying to use placeholders. We are using DB2
which supports insert statements with multiple value tuples, like
this:

insert into myTable values(v1, v2, v3), (v4, v5, v6), (v7, v8,
v9), ....

In our lib, such an intert statement is being built on the fly based
on a parameter specifiying the number of value tuples (usually
1,000). The multiple values speed up the inserts a lot.

Now in order to use placeholders, I need to build SQL like


insert into myTable values(?, ?, ?), (?, ?, ?), (?, ?, ?), ....
(up to 1,000 tuples)

and then bind the values into the 3,000 placeholders. Not sure about
the performance impact. I may run some tests. But sharing of any
experience on this would be appreciated.
 
B

Ben Morrow

Quoth "Henry J. said:
OK, one problem pops up trying to use placeholders. We are using DB2
which supports insert statements with multiple value tuples, like
this:

insert into myTable values(v1, v2, v3), (v4, v5, v6), (v7, v8,
v9), ....

In our lib, such an intert statement is being built on the fly based
on a parameter specifiying the number of value tuples (usually
1,000). The multiple values speed up the inserts a lot.

Now in order to use placeholders, I need to build SQL like


insert into myTable values(?, ?, ?), (?, ?, ?), (?, ?, ?), ....
(up to 1,000 tuples)

and then bind the values into the 3,000 placeholders. Not sure about
the performance impact. I may run some tests. But sharing of any
experience on this would be appreciated.

You may find that using placeholders makes the performance problem go
away. You only need to prepare the statement once (with the placeholders
in), and that is usually what takes the time. Then you can execute it
1,000 times with different bind values, which should be relatively fast.

Ben
 
C

Charlton Wilbur

HJ> OK, one problem pops up trying to use placeholders. We are
HJ> using DB2 which supports insert statements with multiple value
HJ> tuples, like this:

HJ> insert into myTable values(v1, v2, v3), (v4, v5, v6), (v7,
HJ> v8, v9), ....

HJ> In our lib, such an intert statement is being built on the fly
HJ> based on a parameter specifiying the number of value tuples
HJ> (usually 1,000). The multiple values speed up the inserts a
HJ> lot.

I wouldn't dismiss placeholders before running a benchmark. Much of the
delay in your approach may come from parsing the SQL and preparing the
execution plan; if you prepare a statement and execute it multiple times
with different bound values, the statement will only need to be parsed
and prepared once, and this will eliminate a lot of the overhead.

Of course, it may be a quirk of DB2, which I've never used; but it holds
true for Oracle, Informix, MySQL, and Postgres, the databases I have used.

Charlton
 
P

Peter J. Holzer

You may find that using placeholders makes the performance problem go
away. You only need to prepare the statement once (with the placeholders
in), and that is usually what takes the time. Then you can execute it
1,000 times with different bind values, which should be relatively fast.

If DBD::DB2 has proper support for execute_array, you don't even have to
execute it 1000 times - you can execute it once with the data for all
thousand rows. At least for Oracle this is a lot faster than doing lots
of single row inserts.

hp
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top