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 <abaier@xxxxxxx>
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, <REPORTFILE> 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, <REPORTFILE> 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(), <REPORTFILE> line 319.



--
Jonathan Leffler <jonathan.leffler@xxxxxxxxx> #include <disclaimer.h>
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."

Relevant Pages

  • Perl DBI::prepare Question. My head is sore from banging it against the wall. Can you help ease my
    ... 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 ... l line 653, <REPORTFILE> line 319. ...
    (perl.dbi.users)
  • Re: SQL Injection Prevention
    ... executing select sql statement - its about feeding user input to SQL parser. ... vulnerable to sql injection. ... > Provided you don't use dynamic SQL in your stored procedures, AFAIK, you ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... executing select sql statement - its about feeding user input to SQL parser. ... vulnerable to sql injection. ... > Provided you don't use dynamic SQL in your stored procedures, AFAIK, you ...
    (microsoft.public.dotnet.security)
  • Re: Millions of Delete Statements
    ... You mention a cursor. ... Others have mentioned batching the commands, ... so I will throw out one that is less good (executing them ... get the whole SQL statement I don't know of another way to do it). ...
    (comp.databases.ms-sqlserver)
  • Re: sql injection - missed it at bh/defcon + follow on query.
    ... sql injection - missed it at bh/defcon + follow on query. ... >I got thro' a login by putting ... >This list is provided by the SecurityFocus Security Intelligence Alert ...
    (Pen-Test)