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:[EMAIL PROTECTED] Sent: Thursday, November 29, 2007 12:09 PM To: BAIER, ANTHONY (TONY), ATTSI Cc: STILWELL, DAVID B (DAVE), ATTLABS; [email protected] 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 <[EMAIL PROTECTED]> 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. ALB - Can you provide an example? > 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? 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 <= 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. ALB - My SQL does contain single quotes. > 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 <[EMAIL PROTECTED]> #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."
