Bug 10635

Summary: Can't query for CREATE/DELETE in action_logs with SQL reports
Product: Koha Reporter: Nicole C. Engard <nengard>
Component: ReportsAssignee: Galen Charlton <gmcharlt>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: e.betemps, fridolin.somers, katrin.fischer, sophie.meynieux
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Attachments: error

Description Nicole C. Engard 2013-07-23 20:03:32 UTC
Created attachment 19888 [details]
error

I wrote this report:

SELECT p.cardnumber, p.surname, p.firstname, p.streetnumber, p.address, p.address2, p.city, p.state, p.zipcode, p.email, p.phone, p.dateenrolled, p.smsalertnumber, l.action, l.timestamp as 'action taken'
FROM borrowers p
left join action_logs l on (p.borrowernumber=l.object)
where l.module='MEMBERS' and date(l.timestamp) between date_sub(curdate(), interval 1 week) and curdate() and l.action in ('MODIFY','CREATE')

And get the attached error which says I can't use the word 'CREATE' but that's the word in the table ... so maybe we need to remove this restriction or change the wording in the table so that it can be queried.
Comment 1 Galen Charlton 2013-07-23 23:39:51 UTC
The restriction as a whole should not be removed to avoid the risk of a staff user inadvertently creating new tables in the database, but clearly it needs to be made a bit smarter.
Comment 2 Fridolin Somers 2013-11-19 10:05:05 UTC
(In reply to Nicole C. Engard from comment #0)
> Created attachment 19888 [details]

You may use a like :

  SELECT p.cardnumber, p.surname, p.firstname, p.streetnumber, p.address, p.address2, p.city, p.state, p.zipcode, p.email, p.phone, p.dateenrolled, p.smsalertnumber, l.action, l.timestamp AS 'action taken'
  FROM borrowers p
    LEFT JOIN action_logs l ON (p.borrowernumber=l.object)
  WHERE l.module='MEMBERS' 
    AND date(l.timestamp) BETWEEN date_sub(curdate(), interval 1 week)
    AND curdate()
    AND (l.action LIKE 'MODIFY' OR l.action LIKE 'CREAT_')

My 2c
Comment 3 Nicole C. Engard 2013-11-19 14:43:10 UTC
Of course there are ways around it - but I'm a fan of fixing the problem instead of working around it :)
Comment 4 Sophie MEYNIEUX 2019-10-04 08:51:41 UTC
Same thing with DELETE
Comment 5 Emmanuel Bétemps 2019-10-04 09:06:44 UTC
Is it conceivable that the actions' name (DELETE and CREATE) could be changed and replaced with non-forbidden words ?