joomla escape text for inserrt

Question

I'm having problems getting Joomla to insert escaped data

Query is: INSERT INTO #__shopper_orders (id, ordering, state, checked_out, checked_out_time, created_by, modified_by, order_paypal_ref, order_details, fulfillment_status, order_gift, terms_conditions) VALUES (NULL, '', '', '', '', '', '', '', '$newBody', '', '', '');

$newBody is

        $newBody = $db->quote( $emailText );
        $db->query();

the $emailText is

        <h2>Website Order</h2><p>Thank you for your order.&nbsp; We've included a copy of it below.</p>

it throws the error as

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 'you for your order.&nbsp; We\\'ve included a copy of it below.<\/p>\\r\\n<p>Quantiti' at line 3"

It seems to be escaping the already escaped ' character?

Any ideas?

thanks

---

Thought I'd add an update.

I saw nibra below and so tried his code but it didn't work. However I have code that works that looks really similar.

I like the way nibra does the code so I'd like to get it to work. So I've included both the working code and his code (commented out)

            //Insert into orders table
            $newBody = $db->quote( $body );
            $db->query();

            $db = JFactory::getDbo();
            $query ="
            INSERT INTO `#__shopper_orders` 
            (`id`, `ordering`, `state`, `checked_out`, `checked_out_time`, `created_by`, `modified_by`, `order_paypal_ref`, `order_details`, `fulfillment_status`, `order_gift`, `terms_conditions`) 
            VALUES (NULL, '', '1', '', '', '', '', '', $newBody, '1', '', '')
            ";
            $db->setQuery($query);
            $db->query();

            $insertId = $db->insertid();

            /*          $values = [
                'id'                 => null,
                'ordering'           => '',
                'state'              => '1',
                'checked_out'        => '',
                'checked_out_time'   => '',
                'created_by'         => '',
                'modified_by'        => '',
                'order_paypal_ref'   => '',
                'order_details'      => $db->quote($body),
                'fulfillment_status' => '1',
                'order_gift'         => '',
                'terms_conditions'   => '',
            ];
            $query  = $db->getQuery(true);
            $query
                ->insert('#__shopper_orders')
                ->columns(array_keys($values))
                ->values(implode(',', $values))
            ;
            $db->setQuery($query);
            $result = $db->execute();

            $insertId = $db->insertid();*/

Answer 1

First, you should use the QueryBuilder instead of literal SQL. Users of your component might use other RDBMS than MySQL.

Second, JDatabaseDriver::quote is adding the quotes, by default it also escapes the string. Thus quoting $newBody additionally, breaks the SQL.

<?php
$db = \Joomla\CMS\Factory::getDbo();

$values = [
    'id'                 => null,
    'ordering'           => '',
    'state'              => '',
    'checked_out'        => '',
    'checked_out_time'   => '',
    'created_by'         => '',
    'modified_by'        => '',
    'order_paypal_ref'   => '',
    'order_details'      => $db->quote($emailText),
    'fulfillment_status' => '',
    'order_gift'         => '',
    'terms_conditions'   => '',
];
$query  = $db->getQuery(true);
$query
    ->insert('#__shopper_orders')
    ->columns(array_keys($values))
    ->values(implode(',', $values))
;
$db->setQuery($query);
$result = $db->execute();