You have an error in your SQL syntax; creating a trigger in Joomla

Question

I am using mysql and I want to create a trigger in Joomla 3.4.8.

I have a sql code that works in mySQLWorkbench:

If I run it in a Joomla controller:

$db = JFactory::getDbo();
$sql = $db->getQuery(true);
$sql = 'LOCK TABLES id0ap_virtuemart_products WRITE; 
        DROP TRIGGER IF EXISTS update_vm_products ;
        DELIMITER //
        CREATE TRIGGER update_vm_products 
        AFTER UPDATE
        ON id0ap_virtuemart_products FOR EACH ROW
        BEGIN
        INSERT INTO id0ap_virtuemart_notifications
        VALUES ("", NEW.virtuemart_product_id, "product","update");
        END //
        DELIMITER ;
        UNLOCK TABLES;';
$db->setQuery($sql);
$db->execute();

I get:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP TRIGGER IF EXISTS update_vm_products ; DELIMITER // ' at line 2 SQL=LOCK TABLES id0ap_virtuemart_products WRITE; DROP TRIGGER IF EXISTS update_vm_products ; DELIMITER // CREATE TRIGGER update_vm_products AFTER UPDATE ON id0ap_virtuemart_products FOR EACH ROW BEGIN INSERT INTO id0ap_virtuemart_notifications VALUES ("", NEW.virtuemart_product_id, "product","update"); END // DELIMITER ; UNLOCK TABLES;

I can't find the problem. But I have dicovered something strange while I was trying to solve it. If I modify the sql deleting DELIMITERS and the DROP part:

$sql = 'CREATE TRIGGER update_vm_products 
        AFTER UPDATE
        ON id0ap_virtuemart_products FOR EACH ROW
        BEGIN
        INSERT INTO id0ap_virtuemart_notifications
        VALUES ("", NEW.virtuemart_product_id, "product","update");
        END ;';

whether you just try to create the trigger it works and create the trigger, only fails when you add the DROP part. Any idea? Is there an object-oriented way to do this?

Thanks in advance!

Possible duplicate of [PDO support for multiple queries (PDO\_MYSQL, PDO\_MYSQLND)](http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd) — middlestump, May 02 '16 at 14:01
`LOCK TABLES ` and `DROP TRIGGER` are 2 different statements and you need to execute them individually prior the trigger, and also while executing the trigger via PDO or mysqli library you do not need the `delimiter` part. And finally `UNLOCK TABLES;` in a separate statement.Give it a try. — Abhik Chakraborty, May 02 '16 at 14:04
@middlestump I don't understand why is possible duplicate of that. The question has nothing to do with mine and the answer would not be possible in Joomla neither. — Adrián Rodriguez, May 03 '16 at 13:16
@AbhikChakraborty It works! But now I have $sql="query"; $db->setQuery($sql); $db->execute(); 4 times, is there a cleaner way to do this? — Adrián Rodriguez, May 03 '16 at 13:18
Well there no more cleaner way other than mysqli multi query. Also since you are locking and unlocking the tables I don't think you really need them just take them out. Or in addition you can use transaction block. — Abhik Chakraborty, May 03 '16 at 15:47

score 1 · Accepted Answer · answered May 02 '16 at 14:02

1

I'm not a joomla expert, but it seems that whatever mysql api joomla uses to execute the sql statements, can only execute 1 sql statement per call. Try to execute each sql statement on its own.

answered May 02 '16 at 14:02

Shadow

33,525
10
51
64

It works, but I don't like the result: http://prntscr.com/azrd6g I have the same question I asked @Abhik Chakraborty, Is there a cleaner way to do this? Thanks – Adrián Rodriguez May 03 '16 at 13:22
This is not a "dirty" way of executing queries and actually it is safer, than running all statements in one go. If you cannot execute more than one query in a single call, then it is very difficult to execute a successful sql injection attack. – Shadow May 03 '16 at 13:55
The query will go like that there is no user input. It seems to be dirty to me in terms of coding, but if you can't do more than one query at once... – Adrián Rodriguez May 03 '16 at 14:27
You may be able to execute more than one query in a single call, all depends on what mysql api joomla uses to access mysql, how the api and joomla is configured. However, that approach is still not "cleaner". – Shadow May 03 '16 at 14:50

You have an error in your SQL syntax; creating a trigger in Joomla

1 Answers1