mysql - MySQLi queries with php - query strings contain single quotes and curly braces -

i writing php script put dictionary file mysql database. works fine, except in cases when definition strings contain both single quotes , multiple sets of curly braces. 1 of definition strings fails.

(n) (1) {sports} carry-back/bringing ball one's own position (in rugby)/(2) {econ} carryback/carrying on deduction or credit prior year current year (to reduce income tax)

this **mysqli ** error message:

you have error in sql syntax; check manual corresponds mysql server version right syntax use near 's own position (in rugby)/(2) econ', {'(n) (1) {sports} carry-back/bringing ' @ line 1

heres section of script regarding definition string:

$definition = substr($definition_string, 0, $pos);  $definition = substr($definition, 1);  // escape single quote $definition = str_replace(["'"], "''" , $definition);  $mysqli->set_charset("utf8");  $result = $mysqli->query("insert dict (entry, reading, category, definition, entry_number) values ('$entry', '$reading', '$category', '$definition', '$entry_number')");    

i can't figure out why failing , error message isn't helping much. ideas?

i recommend read here. give several different methods on how protect data going database.

here 1 of many ways:

$result = $mysqli->query("insert dict (entry, reading, category, definition, entry_number) values ( '" . $mysqli->escape_string($entry) . "', '" . $mysqli->escape_string($reading) . "', '" . $mysqli->escape_string($category) . "', '" . $mysqli->escape_string($definition) . "', '" . $mysqli->escape_string($entry_number) . "')"); 

another more eloquent solution:

$stmt = $mysqli->prepare("insert dict (entry, reading, category, definition, entry_number) values ( ?, ?, ?, ?, ?)"); $stmt->bind_param('sssss', $entry, $reading, $category, $definition, $entry_number); $stmt->execute(); $result = $stmt->get_result();