I have the following PHP code:
function get_all_labels_by_language_id($language_code, $page_index, $user_id) {
$language_id = $this->get_language_id($language_code);
$users_query = "select eng.label_value, loc.votes, loc.user_id, loc.approved, eng.language_value,
coalesce(loc.language_value)
from labels eng
left outer join
labels loc
on loc.language = " . $language_id . "
and eng.label_value = loc.label_value
and loc.user_id = '" . $user_id . "'
where eng.language = 45
order by loc.language_value";
$data = $this->db->query($users_query)->result_array();
$result = array();
for ($i = 0; $i < count($data); $i++) {
$result[$i]['language_id'] = $language_id;
if (!$data[$i]['user_id']) {
$result[$i]['translate'] = $data[$i];
$result[$i]['alternatives'] = NULL;
$other_records_query = "select eng.label_value, loc.votes, loc.user_id, loc.approved, eng.language_value,
coalesce(loc.language_value)
from labels eng
left outer join
labels loc
on loc.language = " . $language_id . "
and eng.label_value = loc.label_value
where eng.language = 45 and loc.label_value='" . $data[$i]['label_value'] . "'
order by loc.language_value";
$other_records = $this->db->query($other_records_query)->result_array();
for ($k = 0; $k < count($other_records); $k++) {
if ($other_records[$k]['approved'] == '1') {
$result[$i]['translate'] = $other_records[$k];
} else {
$result[$i]['alternatives'][] = $other_records[$k];
}
}
} else {
$result[$i]['translate'] = $data[$i];
$result[$i]['alternatives'] = NULL;
if ($data[$i]['approved'] == '1') {
$other_records_query = "select eng.label_value, loc.votes, loc.user_id, loc.approved, eng.language_value,
coalesce(loc.language_value)
from labels eng
left outer join
labels loc
on loc.language = " . $language_id . "
and eng.label_value = loc.label_value
and loc.approved='1'
where eng.language = 45 and loc.label_value='" . $data[$i]['label_value'] . "'
and approved='0' order by loc.language_value";
$other_records = $this->db->query($other_records_query)->result_array();
for ($k = 0; $k < count($other_records); $k++) {
$result[$i]['alternatives'][] = $other_records[$k];
}
} else {
$other_records_query = "select eng.label_value, loc.votes, loc.user_id, loc.approved, eng.language_value,
coalesce(loc.language_value)
from labels eng
left outer join
labels loc
on loc.language = " . $language_id . "
and eng.label_value = loc.label_value
and loc.approved='1'
where eng.language = 45 and loc.label_value='" . $data[$i]['label_value'] . "'
order by loc.language_value";
$other_records = $this->db->query($other_records_query)->result_array();
for ($k = 0; $k < count($other_records); $k++) {
$result[$i]['alternatives'][] = $other_records[$k];
}
}
}
}
This code works right, but now I've got the following error:
Error Number: 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 't set up correctly. If you're the store owner, please refer to' ' at line 8
select eng.label_value, loc.votes, loc.user_id, loc.approved, eng.language_value, coalesce(loc.language_value) from labels eng left outer join labels loc on loc.language = 24 and eng.label_value = loc.label_value where eng.language = 45 and loc.label_value='It looks like the payment gateway isn't set up correctly. If you're the store owner, please refer to' order by loc.language_value
Filename: Z:\home\localhost\www\system\database\DB_driver.php
Line Number: 330
Please, tell me, how can I fix it? I don't understand where I'm wrong. Thank you in advance.
you should to escape ' in your string literals
'It looks like the payment gateway isn\'t set up correctly. If you\'re the store owner, please refer to'
mysql_real_escape_string is your friend.
You didn't escape string that you're passing to column label_value. It contains characters that break the query.
UPDATE
This looks like CodeIgniter so you should use:
$this->db->escape();
Here's the manual
You are building SQL with unescaped strings - the moment one of the strings contains a quote, you're dead. And by dead I mean: If this is a user-supplied value, you just lost control over your database (Google for "SQL injection").
You must escape all strings, that are not known to be OK - Which method to use is a matter of which DB framework you use: It will provide an escaping function.