From: Mantis B. T. <no...@bu...> - 2012-06-18 16:09:44
|
A NOTE has been added to this issue. ====================================================================== http://bugs.bacula.org/view.php?id=1859 ====================================================================== Reported By: sibio Assigned To: ====================================================================== Project: bacula Issue ID: 1859 Category: sql Reproducibility: always Severity: minor Priority: low Status: acknowledged ====================================================================== Date Submitted: 2012-04-11 09:46 BST Last Modified: 2012-06-18 17:09 BST ====================================================================== Summary: SQL syntax error in UPDATE Counters and INSERT INTO Counters using "Counters.MinValue" and "Counters.MaxValue" Description: Errors occur when using Counter resource (at least) in SQLite context : SQL Syntax error in SQL expression : INSERT INTO Counters [...] Counters.MinValue afaik, this is not proper SQL, specifying "Counters." something in an INSERT or UPDATE statement is redondant and unecessary. At least with sqlite, it yields an SQL syntax error. Steps to Reproduce: Use a counter Resource and reload configuration for the INSERT. Use a counter Resource in a backup for the UPDATE. Additional Information: Changing src/cats/sql_update.c, line 256 AS : "UPDATE Counters SET MinValue=%d,MaxValue=%d,CurrentValue=%d," src/cats/sql_create.c, line 659 AS : Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,MinValue,MaxValue,CurrentValue," solve the issue. ====================================================================== ---------------------------------------------------------------------- (0006286) ebollengier (administrator) - 2012-04-17 09:11 http://bugs.bacula.org/view.php?id=1859#c6286 ---------------------------------------------------------------------- Unfortunately, it breaks MySQL 5.5.... Counters and SQLite are not a top priority, it might be possible to decline SQL queries like in sql_cmd.c to fix definitely the problem. A patch would be appreciated. ---------------------------------------------------------------------- (0006323) galtmann (reporter) - 2012-05-23 17:20 http://bugs.bacula.org/view.php?id=1859#c6323 ---------------------------------------------------------------------- I added a patch to fix the issue for both MySQL and other DBMS by splitting the query depending on the underlying DB. Edit: BTW this problem seems to affect PostgreSQL 9 too. At least it does in my installation. ---------------------------------------------------------------------- (0006407) sibio (reporter) - 2012-06-18 17:09 http://bugs.bacula.org/view.php?id=1859#c6407 ---------------------------------------------------------------------- Have anybody try to protect fields in MySQL using simple quotes? I the SQL query uses a MySQL reserved keyword of some sort. I think UPDATE 'Counters' SET 'MinValue'=%d,'MaxValue'=%d,'CurrentValue'=%d, Should work for all databases. Issue History Date Modified Username Field Change ====================================================================== 2012-04-11 09:46 sibio New Issue 2012-04-17 09:11 ebollengier Note Added: 0006286 2012-04-17 09:12 ebollengier Priority high => low 2012-04-17 09:12 ebollengier Severity major => minor 2012-04-17 09:12 ebollengier Status new => acknowledged 2012-05-23 17:15 galtmann File Added: Split-db_update_counter_record-queries-into-MySQL-sp.patch 2012-05-23 17:16 galtmann Note Added: 0006323 2012-05-23 17:18 galtmann Tag Attached: patch 2012-05-23 17:20 galtmann Note Edited: 0006323 2012-06-18 17:09 sibio Note Added: 0006407 ====================================================================== |