I’m posting this because I encountered this error which I fixed but there might be other issues people have encountered with this. I recently updated my site to Omeka 3.1.2 and I started getting reports of Omeka errors. From the log:
2025-02-07T05:59:40+00:00 ERR (3): Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘Stairways to the Sky: Rice and Life in the Philippines’
It turns out this was the presence of a single quote ('). It turns out, this is only an issue if the field is HTML and also, when I replaced the ’ with ', when I saved it, it replaced ' with a single quote! Wha? Anyway, replacing it with a smart apostrophe (‘) works but you lose a bit of punctuation symmetry unless you want to go through by hand. Ugh.
Anyway, just wondering if anyone else has seen this problem with other characters busting the SQL code. Any advice would be great. Thanks!
Can you post the whole error log entry for that error? It should have a bunch more lines showing a traceback, and that info will help determine where the problem is.
Ok to fix this you need to go into:
RelatedContentPlugin.php
And replace this function:
public function getResultsByElement($element_id, $element_array, $element_weight=1) {
$db = get_db();
$joinCondition = '_advanced_0.record_id = items.id AND _advanced_0.record_type = 'Item' AND _advanced_0.element_id = ';
$select = $db
->select()
->from(array('items' => $db->Item), 'id')
->joinLeft(array('_advanced_0' => $db->ElementText), $joinCondition . $element_id, array())
->where("_advanced_0.text IN ('" . implode("','", $element_array) . "')")
->where("public = 1")
->order("rand()");
$results = $db->fetchCol($select);
// multiply by weight, according to importance of element
return self::countAndMultiply($results, $element_weight);
}
With this one:
public function getResultsByElement($element_id, $element_array, $element_weight = 1) {
$db = get_db();
$joinCondition = '_advanced_0.record_id = items.id AND _advanced_0.record_type = 'Item' AND _advanced_0.element_id = ';
$select = $db
->select()
->from(array('items' => $db->Item), 'id')
->joinLeft(array('_advanced_0' => $db->ElementText), $joinCondition . $element_id, array())
->where("public = 1")
->order("rand()");
if (!empty($element_array)) {
$placeholders = implode(',', array_fill(0, count($element_array), '?'));
$select->where("_advanced_0.text IN ($placeholders)", $element_array);
} else {
return []; // Return empty if there's nothing to search for
}
$results = $db->fetchCol($select);
// Multiply by weight, according to importance of element
return self::countAndMultiply($results, $element_weight);
}
This just escapes it properly and shields the db from sql injection.
