CODEWITHSUNDEEP
phpmysqlmysqli
revolt_101
revolt_101

Reputation: 395

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

I am writing a php script to put a dictionary file into a Mysql database. It works fine, except in certain cases when the definition strings contain both single quotes and multiple sets of curly braces. This is one of the definition strings that fails.

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

This is the **MySQLi ** error message:

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 own position (in rugby)/(2) econ', {'(n) (1) {sports} carry-back/bringing the ' at line 1

Heres the section of the script regarding the 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 INTO dict (entry, reading, category, definition, entry_number) VALUES ('$entry', '$reading', '$category', '$definition', '$entry_number')");

I can't figure out why its failing and the error message isn't helping much. Any ideas?

Upvotes: 0

Views: 416

Answers (1)

Blue
Blue

Reputation: 22911

I recommend you read about this here. They give several different methods on how to protect the data going into the database.

Here is one of the many ways:

$result = $mysqli->query("INSERT INTO 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 INTO dict (entry, reading, category, definition, entry_number) VALUES (
?, ?, ?, ?, ?)");
$stmt->bind_param('sssss', $entry, $reading, $category, $definition, $entry_number);
$stmt->execute();
$result = $stmt->get_result();

Upvotes: 1

Related Questions