1

I'm relatively new to using mysqli prepared statements and am having problems inserting strings that contain forward slashes.

Here's an example of the code I'm using:

// $mysqli connection
$rxcui = null;
$name = null;
$synonym = null;
$tty = null;

$sql = "INSERT INTO fda_oral (id, rxcui, `name`, `synonym`, tty, hide, created, updated) VALUES (NULL, ?, ?, ?, ?, 0, NOW(), NOW() )";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ssss", $rxcui, $name, $synonym, $tty);

$rxcui = "201903";
$name = "acyclovir 40 MG/ML Oral Suspension [Zovirax]";
$synonym = "Zovirax 40 MG/ML Oral Suspension";
$tty = "SBD";

if ($stmt->execute()) {
    echo "success";
} else {
    echo "failed";
}

This is failing.

If I use stings without the slashes I am successful, eg)

$name = "acyclovir 400 MG [Zovirax]";
$synonym = "Zovirax 400 MG Oral Suspension";

From everything I've read, it sounds as if the prepared statements should deal with any special characters in the strings - in fact, it seems like this is the recommended solution when dealing with strings that might have special characters.

Even though it doesn't seem like it should be necessary, I did try

 $name = $mysqli->real_escape_string($name);

I'm sure I'm missing something simple here but I just can't figure out where I'm going wrong.

Zarwell
  • 111
  • 8
  • 2
    I'm guessing you're getting 'failed' instead of 'success', since you say it's failing. When a query does not work as you expect, check for [mysqli errors](http://php.net/manual/en/mysqli.error.php) to find out **why** it's failing. – aynber Jul 01 '21 at 14:49
  • 1
    Try turning on error reporting put this line: `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` above your `$mysqli = new mysqli(...);` line and trying again... – Steven Jul 01 '21 at 14:56
  • 2
    Ugh, I'm an idiot. When I turned on the error reporting I realized I was trying to insert strings that were exceeding the character limit for the database field. The only pattern I had noticed was that the failures always happened when the input contained slashes, but now I see that every time the input contained slashes the strings were also longer. Rookie mistake - thank you for the advice. – Zarwell Jul 01 '21 at 15:12
  • 2
    You had two mistakes: 1. You didn't enable mysqli error reporting. 2. You still think that there are special characters. Prepared statements don't care about the contents of your string. It's just data. Forward slashes might be considered special characters in a context of a URL, but not SQL – Dharman Jul 01 '21 at 15:22

0 Answers0