Perl DBI::prepare Question. My head is sore from banging it against the wall. Can you help ease my

------_=_NextPart_001_01C832A1.C3CC1EE2
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable


Dave, DBI User Group,

Can you take a quick look at the code block below and error messages
being generated when executing. =20

Any idea why the last 2 characters of the $sql variable are getting
chopped off when "prepare" is executed?

How do I prevent the program from termininating and letting me handle
the error handling? I tried the following DBI::CONNECT statement put
it did not help.

my $dbh =3D DBI->connect($data_source, $dbUser, $dbPassword, {
PrintError=3D>0, RaiseError=3D>0, AutoCommit=3D>0 });



Code Block in Error:

$sql =3D "insert into
odba_user.dbh_high_memory_read_sqls

(report_id, query_no,
buffer_gets, =20
no_executions, sql_text)

values

($reportId, $queryNumber,
$readCount, =20
$execCount, '$queryText')";

=20

print "\n\nflag11a sql [$sql]\n\n";

$sth =3D $dbh->prepare("$sql");

undef $rc;

$rc =3D $sth->execute();

=20

unless (defined $rc) {

printf LOGFILE "statement
execution fail
ed:\n\"$sql\"\n$DBI::errstr\n";

# ignore these insert errors

# $errorCode =3D 1;

print "flag11\n";

}


My Print Statement of $sql

flag11a sql [insert into odba_user.dbh_high_memory_read_sqls

(report_id, query_no,
buffer_gets, =20
no_executions, sql_text)

values

(570, 8, 620184,

206727, 'select job,
nvl2(last_date, 1,=20
0) from sys.job$ where (((:1 <=3D next_date) and (next_date <=3D :2)) or
((last_date
is null) and (next_date < :3))) and (field1 =3D :4 or (field1 =3D 0 and
''Y'' =3D :5)
) and (this_date is null) order by next_date, job')]

=20
Perl DBI::PrintError Results (where is the trailing single quote ')
=20
DBD::Oracle::db prepare failed: ORA-01756: quoted string not properly
terminated
(DBD ERROR: OCIStmtPrepare) [for Statement "insert into
odba_user.dbh_high_mem
ory_read_sqls

(report_id, query_no,
buffer_gets, =20
no_executions, sql_text)

values

(570, 8, 620184,

206727, 'select job,
nvl2(last_date, 1,=20
0) from sys.job$ where (((:1 <=3D next_date) and (next_date <=3D :2)) or
((last_date
is null) and (next_date < :3))) and (field1 =3D :4 or (field1 =3D 0 and
''Y'' =3D :5)
) and (this_date is null) order by next_date, job at
.../../bin/DBhealthParseDB.p
l line 653, line 319.

Can't call method "execute" on an undefined value at
.../../bin/DBhealthParseDB.
l line 655, line 319.

Issuing rollback() for database handle being DESTROY'd without explicit
disconn
ct(), line 319.





Tony Baier
AT&T ITO DBA Support Services
732 420-2140 office
732 276-6063 vo
732 294-3015 pager
7322943015@page.metrocall.com



------_=_NextPart_001_01C832A1.C3CC1EE2--

RE: Perl DBI::prepare Question. My head is sore from banging it against the wall. Can you help eas

-----Original Message-----
From: BAIER, ANTHONY (TONY), ATTSI [mailto:abaier@att.com]=20
Sent: Thursday, November 29, 2007 11:06 AM
To: STILWELL, DAVID B (DAVE), ATTLABS; dbi-users@perl.org
Subject: Perl DBI::prepare Question. My head is sore from banging it
against the wall. Can you help ease my pain?


Dave, DBI User Group,

[>>] ...

Issuing rollback() for database handle being DESTROY'd without explicit
disconn
ct(), line 319.

[>>]=20
Well right off hand I don't see it but can suggest (from past
experience!) that you use placeholders ... or at the least change the
quote style... qq{insert ... some quoted '$string'}...=20

The placeholders made my life much easier...

Example...=20

$sql =3D 'insert ... values (?,?,?,?,?)'
....
$sth =3D $dbh->prepare($sql) or die DBI->errstr;
$rc =3D $sth->execute($reportId, $queryNumber,$readCount, $execCount,
$queryText);


Hope this gives you some ideas...

jwm

Re: Perl DBI::prepare Question. My head is sore from banging it against the wall. Can you help ease

------=_Part_9562_27910406.1196356111933
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

There's no solution to your problem below - there is some commentary that
may, or may not, be of relevance.

On Nov 29, 2007 8:06 AM, BAIER, ANTHONY (TONY), ATTSI
wrote:

> Can you take a quick look at the code block below and error messages
> being generated when executing.
>
> Any idea why the last 2 characters of the $sql variable are getting
> chopped off when "prepare" is executed?
>
> How do I prevent the program from termininating and letting me handle
> the error handling? I tried the following DBI::CONNECT statement put
> it did not help.
>
> my $dbh = DBI->connect($data_source, $dbUser, $dbPassword, {
> PrintError=>0, RaiseError=>0, AutoCommit=>0 });
>

If you are going to have DBI not act on errors, you must do the error
checking yourself.
If you are debugging a problem, use PrintError => 1 and/or RaiseError => 1.

Code Block in Error:
>
> $sql = "insert into
> odba_user.dbh_high_memory_read_sqls
> (report_id, query_no,buffer_gets,
> no_executions, sql_text)
> values
> ($reportId, $queryNumber,
> $readCount,
> $execCount, '$queryText')";
>
>

This is a bad way of processing input data with SQL -- you are setting
yourself up for an SQL injection attack.

For a wonderful, comical demonstration of an SQL injection attack, see:
http://xkcd.com/327/

Use placeholders - or, learn about $dbh->quote.



> print "\n\nflag11a sql [$sql]\n\n";
>
> $sth = $dbh->prepare("$sql");
>

No error check?


> undef $rc;
> $rc = $sth->execute();
>

An odd way of doing business...

unless (defined $rc) {
> printf LOGFILE "statement
> execution failed:\n\"$sql\"\n$DBI::errstr\n";
> # ignore these insert errors
> # $errorCode = 1;
> print "flag11\n";
> }
>
>
> My Print Statement of $sql
>
> flag11a sql [insert into odba_user.dbh_high_memory_read_sqls
> (report_id, query_no, buffer_gets,
> no_executions, sql_text)
> values
> (570, 8, 620184,
> 206727, 'select job,
> nvl2(last_date, 1,
> 0) from sys.job$ where (((:1 <= next_date) and (next_date <= :2)) or
> ((last_date
> is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and
> ''Y'' = :5)
> ) and (this_date is null) order by next_date, job')]
>


This looks correct - is there a problem here?


> Perl DBI::PrintError Results (where is the trailing single quote ')
>
> DBD::Oracle::db prepare failed: ORA-01756: quoted string not properly
> terminated
> (DBD ERROR: OCIStmtPrepare) [for Statement "insert into
> odba_user.dbh_high_mem
> ory_read_sqls
>
> (report_id, query_no, buffer_gets,
> no_executions, sql_text)
> values
> (570, 8, 620184,
> 206727, 'select job,
> nvl2(last_date, 1,
> 0) from sys.job$ where (((:1 <= next_date) and (next_date <= :2)) or
> ((last_date
> is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and
> ''Y'' = :5)
> ) and (this_date is null) order by next_date, job at
> ../../bin/DBhealthParseDB.p
> l line 653, line 319.
>

This I agree seems to be missing some data - two characters as you say. You
are fortunate that your SQL does not contain any single quotes, of course --
that's the SQL injection issue above.



> Can't call method "execute" on an undefined value at
> ../../bin/DBhealthParseDB.
> l line 655, line 319.
>

This is because you ignored the error from prepare and blithely tried to use
the $sth that wasn't available.


>
> Issuing rollback() for database handle being DESTROY'd without explicit
> disconn
> ct(), line 319.
>


--
Jonathan Leffler #include
Guardian of DBD::Informix - v2007.0914 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

------=_Part_9562_27910406.1196356111933--

RE: Perl DBI::prepare Question. My head is sore from banging it against the wall. Can you help ease

Jonathan

First off thank you for your commentary, very insightful. I am making
the code changes now to add more error checking and remove the code
oddity that you pointed out.

The problem that I am having is that the "prepare" execution is
truncating the last two characters of the $sql variable value. The SQL
text that I am trying to insert into the table does contain single
quotes.

See my Reponses to your questions below. Maybe SQL Code injection is
having a negative affect on this particular execution. There were 17
previous execution of this statement with different SQL text values that
executed without error, and they contained single quotes too. Why this
particular insert fails is beyond me.

How can I not have the program automatically terminate when the prepare
fails?

I look forward to your response my head is starting to feel better, but
it still hurts.

Regards,
Tony


-----Original Message-----
From: Jonathan Leffler [mailto:jonathan.leffler@gmail.com]=20
Sent: Thursday, November 29, 2007 12:09 PM
To: BAIER, ANTHONY (TONY), ATTSI
Cc: STILWELL, DAVID B (DAVE), ATTLABS; dbi-users@perl.org
Subject: Re: Perl DBI::prepare Question. My head is sore from banging it
against the wall. Can you help ease my pain?

There's no solution to your problem below - there is some commentary
that
may, or may not, be of relevance.

On Nov 29, 2007 8:06 AM, BAIER, ANTHONY (TONY), ATTSI
wrote:

> Can you take a quick look at the code block below and error messages
> being generated when executing.
>
> Any idea why the last 2 characters of the $sql variable are getting
> chopped off when "prepare" is executed?
>
> How do I prevent the program from termininating and letting me handle
> the error handling? I tried the following DBI::CONNECT statement put
> it did not help.
>
> my $dbh =3D DBI->connect($data_source, $dbUser, $dbPassword, {
> PrintError=3D>0, RaiseError=3D>0, AutoCommit=3D>0 });
>

If you are going to have DBI not act on errors, you must do the error
checking yourself.
If you are debugging a problem, use PrintError =3D> 1 and/or RaiseError =
=3D>
1.

Code Block in Error:
>
> $sql =3D "insert into
> odba_user.dbh_high_memory_read_sqls
> (report_id,
query_no,buffer_gets,
> no_executions, sql_text)
> values
> ($reportId, $queryNumber,
> $readCount,
> $execCount, '$queryText')";
>
>

This is a bad way of processing input data with SQL -- you are setting
yourself up for an SQL injection attack.

For a wonderful, comical demonstration of an SQL injection attack, see:
http://xkcd.com/327/

Use placeholders - or, learn about $dbh->quote.

ALB - Can you provide an example?



> print "\n\nflag11a sql [$sql]\n\n";
>
> $sth =3D $dbh->prepare("$sql");
>

No error check?


> undef $rc;
> $rc =3D $sth->execute();
>

An odd way of doing business...

unless (defined $rc) {
> printf LOGFILE "statement
> execution failed:\n\"$sql\"\n$DBI::errstr\n";
> # ignore these insert errors
> # $errorCode =3D 1;
> print "flag11\n";
> }
>
>
> My Print Statement of $sql
>
> flag11a sql [insert into odba_user.dbh_high_memory_read_sqls
> (report_id, query_no,
buffer_gets,
> no_executions, sql_text)
> values
> (570, 8, 620184,
> 206727, 'select job,
> nvl2(last_date, 1,
> 0) from sys.job$ where (((:1 <=3D next_date) and (next_date <=3D :2)) =
or
> ((last_date
> is null) and (next_date < :3))) and (field1 =3D :4 or (field1 =3D 0 =
and
> ''Y'' =3D :5)
> ) and (this_date is null) order by next_date, job')]
>


This looks correct - is there a problem here? =20

ALB- The quote is missing. In the $sql variable print output above the
single quote is there.


> Perl DBI::PrintError Results (where is the trailing single quote ')
>
> DBD::Oracle::db prepare failed: ORA-01756: quoted string not properly
> terminated
> (DBD ERROR: OCIStmtPrepare) [for Statement "insert into
> odba_user.dbh_high_mem
> ory_read_sqls
>
> (report_id, query_no,
buffer_gets,
> no_executions, sql_text)
> values
> (570, 8, 620184,
> 206727, 'select job,
> nvl2(last_date, 1,
> 0) from sys.job$ where (((:1 <=3D next_date) and (next_date <=3D :2)) =
or
> ((last_date
> is null) and (next_date < :3))) and (field1 =3D :4 or (field1 =3D 0 =
and
> ''Y'' =3D :5)
> ) and (this_date is null) order by next_date, job at
> ../../bin/DBhealthParseDB.p
> l line 653, line 319.
>

This I agree seems to be missing some data - two characters as you say.
You
are fortunate that your SQL does not contain any single quotes, of
course --
that's the SQL injection issue above.

ALB - My SQL does contain single quotes.



> Can't call method "execute" on an undefined value at
> ../../bin/DBhealthParseDB.
> l line 655, line 319.
>

This is because you ignored the error from prepare and blithely tried to
use
the $sth that wasn't available.


>
> Issuing rollback() for database handle being DESTROY'd without
explicit
> disconn
> ct(), line 319.
>


--=20
Jonathan Leffler #include
Guardian of DBD::Informix - v2007.0914 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to
be
amused."

RE: Perl DBI::prepare Question. My head is sore from banging it against the wall. Can you help eas

THANKS. IT Works!!! Using "placeholders" solved the problem. I
will take this knowledge with me and move forward.

My head feels much better thanks to all.

- Tony :)=20

-----Original Message-----
From: Moon, John [mailto:John.Moon@dms.myflorida.com]=20
Sent: Thursday, November 29, 2007 11:57 AM
To: dbi-users@perl.org
Subject: RE: Perl DBI::prepare Question. My head is sore from banging it
against the wall. Can you help ease my pain?


-----Original Message-----
From: BAIER, ANTHONY (TONY), ATTSI [mailto:abaier@att.com]=20
Sent: Thursday, November 29, 2007 11:06 AM
To: STILWELL, DAVID B (DAVE), ATTLABS; dbi-users@perl.org
Subject: Perl DBI::prepare Question. My head is sore from banging it
against the wall. Can you help ease my pain?


Dave, DBI User Group,

[>>] ...

Issuing rollback() for database handle being DESTROY'd without explicit
disconn
ct(), line 319.

[>>]=20
Well right off hand I don't see it but can suggest (from past
experience!) that you use placeholders ... or at the least change the
quote style... qq{insert ... some quoted '$string'}...=20

The placeholders made my life much easier...

Example...=20

$sql =3D 'insert ... values (?,?,?,?,?)'
....
$sth =3D $dbh->prepare($sql) or die DBI->errstr;
$rc =3D $sth->execute($reportId, $queryNumber,$readCount, $execCount,
$queryText);


Hope this gives you some ideas...

jwm