CDR Tickets

Issue Number 3026
Summary [Internal] Modify CTGovMarkDuplicate.py to use SQL placeholders
Created 2009-11-17 17:33:14
Issue Type Bug
Submitted By Kline, Bob (NIH/NCI) [C]
Assigned To Englisch, Volker (NIH/NCI) [C]
Status Closed
Resolved 2010-11-12 16:35:39
Resolution Fixed
Path /home/bkline/backups/jira/ocecdr/issue.107354
Description

BZISSUE::4702
BZDATETIME::2009-11-17 17:33:14
BZCREATOR::Bob Kline
BZASSIGNEE::Volker Englisch
BZQACONTACT::Bob Kline

If the user enters values with apostrophes on the CGI form for modifying the duplicate status of a CT.gov trial the SQL breaks (because the queries are built using direct string manipulation instead of the DBI placeholder mechanism. Please fix.

Comment entered 2010-01-15 14:31:22 by Englisch, Volker (NIH/NCI) [C]

BZDATETIME::2010-01-15 14:31:22
BZCOMMENTOR::Volker Englisch
BZCOMMENT::1

Bob, the version that's currently in production is different from what's in SVN.
Do you maybe have changes in your working directory that still need to be versioned?

Comment entered 2010-01-15 16:45:13 by Kline, Bob (NIH/NCI) [C]

BZDATETIME::2010-01-15 16:45:13
BZCOMMENTOR::Bob Kline
BZCOMMENT::2

(In reply to comment #1)
> Bob, the version that's currently in production is different from what's in
> SVN.
> Do you maybe have changes in your working directory that still need to be
> versioned?

The version in SVN is what you should be working from. CIAT ran into a bug in the script when they were testing on Franck for issue #4689. I fixed that bug, checked the fix into version control, and installed it on Franck so the users could resume their testing. I noticed further problems, though, so I created this issue to address them. So when you're done here and the users have tested then final version of the script it can be pushed to production.

Comment entered 2010-01-19 11:06:11 by Englisch, Volker (NIH/NCI) [C]

BZDATETIME::2010-01-19 11:06:11
BZCOMMENTOR::Volker Englisch
BZCOMMENT::3

(In reply to comment #0)
> If the user enters values with apostrophes on the CGI form for modifying the
> duplicate status of a CT.gov trial the SQL breaks (because the queries are
> built using direct string manipulation instead of the DBI placeholder
> mechanism. Please fix.

It appears there was a reason for writing the query the way it was. When changing to use the DBI placeholder the following error message is displayed:

Failure updating CTGov Protocol NCT00000611 with 599223:
Parameter Information cannot be derived from SQL statements
with sub-select queries. Set parameter information before
preparing command.

I'll try to find a different approach to solve the problem.

Comment entered 2010-11-10 14:41:41 by Englisch, Volker (NIH/NCI) [C]

BZDATETIME::2010-11-10 14:41:41
BZCOMMENTOR::Volker Englisch
BZCOMMENT::4

... and less than a year later this has been fixed. :-)

I've replaced the sub-query to allow the SQL parameter substitution to be allowed.
The following program has been updated
CTGovMarkDuplicate.py - R9949

This is ready for review on MAHLER.

Comment entered 2010-11-12 09:24:28 by Kline, Bob (NIH/NCI) [C]

BZDATETIME::2010-11-12 09:24:28
BZCOMMENTOR::Bob Kline
BZCOMMENT::5

Looks good; please promote.

Comment entered 2010-11-12 16:34:06 by Englisch, Volker (NIH/NCI) [C]

BZDATETIME::2010-11-12 16:34:06
BZCOMMENTOR::Volker Englisch
BZCOMMENT::6

The program has been copied to FRANCK and BACH:
CTGovMarkDuplicate.py - R9949

Please verify on BACH and close this bug.

Comment entered 2010-11-12 16:35:39 by Kline, Bob (NIH/NCI) [C]

BZDATETIME::2010-11-12 16:35:39
BZCOMMENTOR::Bob Kline
BZCOMMENT::7

Thanks!

Elapsed: 0:00:00.001555