How to use prepared statements in Joomla?

Question

How to use Prepare methods in joomla model?
for example in pdo we use :

db->prepare('INSERT INTO tbl (`city`,`date`,`uid`,`title`) VALUES(:city,:date,:uid,:title)');  

How can I do it in the Joomla!

Oneness

Sadly, there are currently no prepared statements for Joomla. Lodder's answer below uses "the next best thing" to properly quote strings in the query. This is a related answer that speaks on security. – Sosthenna

Answer 1

In Joomla, you always stick to the API which caters for the supported database types, like so:

$db = JFactory::getDbo();

$query = $db->getQuery(true);
$columns = array('city', 'date', 'uid', 'title');
$values = array($db->quote('value1'), $db->quote('value2'), $db->quote('value3'), $db->quote('value4'));

// Prepare the insert query.
$query
    ->insert($db->quoteName('#__tablename')) //make sure you keep #__
    ->columns($db->quoteName($columns))
    ->values(implode(',', $values));

$db->setQuery($query);
$db->query();

and for Joomla 3.x, you can replace $db->query(); with $db->execute();

---

Update:

As far as I know, Joomla 4 will use prepared statements in core. Here is a something I've mocked up, however have not tested:

    use Joomla\CMS\Factory;
    use Joomla\Database\ParameterType;

    $db = Factory::getDbo();

    // Your data
    $city = $db->quote('London');
    $date = $db->quote('21/01/2020');
    $uid = $db->quote(1234);
    $title = $db->quote('My Title');

    // Prepared query
    $query = $db->getQuery(true)
        ->insert($db->quoteName('#__tablename'))
        ->columns([
            $db->quoteName('city'),
            $db->quoteName('date'),
            $db->quoteName('uid'),
            $db->quoteName('title'),
        ])
        ->values(':city, :date, :uid, :title')
        ->bind(':city', $city, ParameterType::STRING)
        ->bind(':date', $date)
        ->bind(':uid', $uid, ParameterType::INTEGER)
        ->bind(':title', $title, ParameterType::STRING);

    $db->setQuery($query);
    $db->execute();

Tablet

And what about if I want to use prepare statements in a query like SELECT * FROM a WHERE group = :group – Caesium

@OceanicSix - https://mcmap.net/q/1623131/-joomla-database-methods – Tablet

it is not working to me... "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 ':taste' at line 3 SQL=SELECT * FROM food WHERE taste = :taste". I am using MySQL, might be that the problem? – Caesium

If you're having problems with your code, please ask a new question showing your code rather than commenting on here. – Tablet

Is this a prepared statement in Joomla? – Official

@RNKushwaha No, it is no prepared statement. It uses a regular statement. This syntax does not provide support for prepared statements. Maybe there will be support in the future but not now as far as I know of. – Robinette

@Tablet you may like to update this one: joomla.stackexchange.com/a/12120/12352 – Sosthenna

Calling quote() is unnecessary on bound values, correct? I don't have a J4 installation to test with. @Lod – Sosthenna

@Sosthenna It's always good practice to sanitize values when inserting content into the database. Prepared statements don't 100% prevent SQL injection. – Tablet

quote() isn't a value sanitising method. – Sosthenna

"Escape" then, you know what I mean – Tablet