admin管理员组文章数量:1122832
I have the below PHP function that queries some custom database tables and outputs the result. This generally works great, except for when I put an apostrophe in the query; for example, "Men's" or "Women's" causes errors like below. How can I solve this? I'm a bit of a SQL novice, so I'm sure I'm doing something obviously wrong.
[03-Oct-2024 16:12:10 UTC] WordPress database error 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 's%' OR `description` LIKE '%Men\\'s%' OR `code` LIKE '%Men\\'s%') ORDER BY `titl' at line 1 for query SELECT * FROM `83kg5a_activity_posts` WHERE ( `title` LIKE '%Men\\'s%' OR `description` LIKE '%Men\\'s%' OR `code` LIKE '%Men\\'s%') ORDER BY `title` ASC, `code` ASC made by do_action('wp_ajax_activity_list'), WP_Hook->do_action, WP_Hook->apply_filters, ActivityPlugin\ajax_activity_list, activity_include, include('/plugins/activity-plugin/views/activity-list.php'), ActivityPlugin\get_activities
/**
* Get activities, optionally with a query
*
* @param array<mixed> $options
* @return array|null
*/
function get_activities(array $options = []): ?array {
global $wpdb;
/**
* Get the settings
*/
$settings = get_settings();
/**
* Convert query
*/
if (array_key_exists("q", $_GET) && $_GET["q"]) {
$options[] = [
[
"key" => "title",
"operator" => "LIKE",
"value" => "%" . $wpdb->esc_like($_GET["q"]) . "%",
],
[
"key" => "description",
"operator" => "LIKE",
"value" => "%" . $wpdb->esc_like($_GET["q"]) . "%",
],
[
"key" => "code",
"operator" => "LIKE",
"value" => "%" . $wpdb->esc_like($_GET["q"]) . "%",
],
];
}
/**
* Convert terms
*/
foreach ($_GET["terms"] as $data) {
$options[] = [
"key" => $data["name"],
"operator" => "=",
"value" => $data["value"],
];
}
/**
* Convert DOW
*/
if (array_key_exists("dow", $_GET) && count($_GET["dow"]) < 7) {
$options[] = array_map(function($day) use ($wpdb) {
return [
"key" => "schedule",
"operator" => "LIKE",
"value" => "%" . $wpdb->esc_like($day) . "%",
];
}, $_GET["dow"]);
}
/**
* Prepare the query
*/
$query = "SELECT * FROM `{$wpdb->prefix}activity_posts`";
/**
* Append the options to the query
*/
if ($options) {
$query .= " WHERE";
/**
* Append the options
*/
foreach ($options as $data) {
if (array_key_exists(0, $data)) {
$query .= " (";
foreach ($data as $value) {
$query .= " `{$value["key"]}` {$value["operator"]} '{$value["value"]}' OR";
}
$query = rtrim($query, " OR");
$query .= ") AND";
} else {
$query .= " `{$data["key"]}` {$data["operator"]} '{$data["value"]}' AND";
}
}
$query = rtrim($query, " AND");
}
/**
* Convert age
*/
$queried_min = (array_key_exists("age_minimum", $_GET) ? intval($_GET["age_minimum"]) : $settings["age_limit_min"]) * 12;
$queried_max = (array_key_exists("age_maximum", $_GET) ? intval($_GET["age_maximum"]) : $settings["age_limit_senior"]) * 12;
$min_query = "";
$max_query = "";
if ($queried_min !== $settings["age_limit_min"] * 12) {
$min_query = " `age_minimum` >= {$queried_min}";
}
if ($queried_min !== $settings["age_limit_min"] * 12 || $queried_max !== $settings["age_limit_senior"] * 12) {
if ($queried_max >= $settings["age_limit_senior"] * 12) {
$max_query .= " `age_maximum` >= {$queried_max}";
} elseif ($queried_max >= $settings["age_limit_adult"] * 12) {
$max_query .= " `age_maximum` >= {$queried_max} AND `age_maximum` <= " . ($settings["age_limit_senior"] * 12);
} elseif ($queried_max > $settings["age_limit_min"] * 12) {
$max_query .= " `age_maximum` <= {$queried_max}";
}
}
if ($min_query || $max_query) {
$query .= $options ? " AND" : " WHERE";
if ($min_query && $max_query) {
$query .= " ({$min_query} AND {$max_query})";
} elseif ($min_query) {
$query .= $min_query;
} elseif ($max_query) {
$query .= $max_query;
}
}
/**
* Set the order
*/
$query .= " ORDER BY `title` ASC, `code` ASC";
/**
* Log the query
*/
if (WP_DEBUG_LOG) {
error_log($query);
}
/**
* Get all cached activities from the database
*/
return $wpdb->get_results($query);
}
I have the below PHP function that queries some custom database tables and outputs the result. This generally works great, except for when I put an apostrophe in the query; for example, "Men's" or "Women's" causes errors like below. How can I solve this? I'm a bit of a SQL novice, so I'm sure I'm doing something obviously wrong.
[03-Oct-2024 16:12:10 UTC] WordPress database error 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 's%' OR `description` LIKE '%Men\\'s%' OR `code` LIKE '%Men\\'s%') ORDER BY `titl' at line 1 for query SELECT * FROM `83kg5a_activity_posts` WHERE ( `title` LIKE '%Men\\'s%' OR `description` LIKE '%Men\\'s%' OR `code` LIKE '%Men\\'s%') ORDER BY `title` ASC, `code` ASC made by do_action('wp_ajax_activity_list'), WP_Hook->do_action, WP_Hook->apply_filters, ActivityPlugin\ajax_activity_list, activity_include, include('/plugins/activity-plugin/views/activity-list.php'), ActivityPlugin\get_activities
/**
* Get activities, optionally with a query
*
* @param array<mixed> $options
* @return array|null
*/
function get_activities(array $options = []): ?array {
global $wpdb;
/**
* Get the settings
*/
$settings = get_settings();
/**
* Convert query
*/
if (array_key_exists("q", $_GET) && $_GET["q"]) {
$options[] = [
[
"key" => "title",
"operator" => "LIKE",
"value" => "%" . $wpdb->esc_like($_GET["q"]) . "%",
],
[
"key" => "description",
"operator" => "LIKE",
"value" => "%" . $wpdb->esc_like($_GET["q"]) . "%",
],
[
"key" => "code",
"operator" => "LIKE",
"value" => "%" . $wpdb->esc_like($_GET["q"]) . "%",
],
];
}
/**
* Convert terms
*/
foreach ($_GET["terms"] as $data) {
$options[] = [
"key" => $data["name"],
"operator" => "=",
"value" => $data["value"],
];
}
/**
* Convert DOW
*/
if (array_key_exists("dow", $_GET) && count($_GET["dow"]) < 7) {
$options[] = array_map(function($day) use ($wpdb) {
return [
"key" => "schedule",
"operator" => "LIKE",
"value" => "%" . $wpdb->esc_like($day) . "%",
];
}, $_GET["dow"]);
}
/**
* Prepare the query
*/
$query = "SELECT * FROM `{$wpdb->prefix}activity_posts`";
/**
* Append the options to the query
*/
if ($options) {
$query .= " WHERE";
/**
* Append the options
*/
foreach ($options as $data) {
if (array_key_exists(0, $data)) {
$query .= " (";
foreach ($data as $value) {
$query .= " `{$value["key"]}` {$value["operator"]} '{$value["value"]}' OR";
}
$query = rtrim($query, " OR");
$query .= ") AND";
} else {
$query .= " `{$data["key"]}` {$data["operator"]} '{$data["value"]}' AND";
}
}
$query = rtrim($query, " AND");
}
/**
* Convert age
*/
$queried_min = (array_key_exists("age_minimum", $_GET) ? intval($_GET["age_minimum"]) : $settings["age_limit_min"]) * 12;
$queried_max = (array_key_exists("age_maximum", $_GET) ? intval($_GET["age_maximum"]) : $settings["age_limit_senior"]) * 12;
$min_query = "";
$max_query = "";
if ($queried_min !== $settings["age_limit_min"] * 12) {
$min_query = " `age_minimum` >= {$queried_min}";
}
if ($queried_min !== $settings["age_limit_min"] * 12 || $queried_max !== $settings["age_limit_senior"] * 12) {
if ($queried_max >= $settings["age_limit_senior"] * 12) {
$max_query .= " `age_maximum` >= {$queried_max}";
} elseif ($queried_max >= $settings["age_limit_adult"] * 12) {
$max_query .= " `age_maximum` >= {$queried_max} AND `age_maximum` <= " . ($settings["age_limit_senior"] * 12);
} elseif ($queried_max > $settings["age_limit_min"] * 12) {
$max_query .= " `age_maximum` <= {$queried_max}";
}
}
if ($min_query || $max_query) {
$query .= $options ? " AND" : " WHERE";
if ($min_query && $max_query) {
$query .= " ({$min_query} AND {$max_query})";
} elseif ($min_query) {
$query .= $min_query;
} elseif ($max_query) {
$query .= $max_query;
}
}
/**
* Set the order
*/
$query .= " ORDER BY `title` ASC, `code` ASC";
/**
* Log the query
*/
if (WP_DEBUG_LOG) {
error_log($query);
}
/**
* Get all cached activities from the database
*/
return $wpdb->get_results($query);
}
1 Answer
Reset to default 3Using the wpdb::prepare() method is recommended for creating queries properly and avoiding SQL injection.
For a basic solution, use the addslashes() function, but this is not sufficient for preventing SQL injection.
See Mysql + php with special characters like '(Apostrophe) and " (Quotation mark).
本文标签： databaseSQL errors when querying for something with apostrophes
版权声明：本文标题：database - SQL errors when querying for something with apostrophes 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736285172a1927403.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论