2

I am using the below-prepared statement to insert into MySQL. If I try and insert something with $descrip containing a " (double quote) the insert stops at this point.

Example: Trying to insert 16" Solid Steel Tube The entry into the table row only shows 16 and stops at the " (double quote) and wont show the rest of $descrip.

What am I doing wrong?

$stmt = $db->prepare("INSERT INTO line_items (quote_id, part, descrip, qty, price, net, notes, datasheet) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param("ssssssss", $quote_id, $part, $descrip, $qty, $price, $net, $notes, $datasheet);

foreach($_POST['part'] as $i => $item) {

    $part       = $item;
    $descrip   = $_POST['descrip'][$i];  //This wont enter something with double qutoes such as 16" Solid Steel Tube
    $qty        = $_POST['qty'][$i];
    $price      = $_POST['price'][$i];
    $net        = $_POST['net'][$i];
    $notes      = $_POST['notes'][$i];
    $datasheet  = $_POST['datasheet'][$i];

    $stmt->execute();
}

EDIT: FYI- I am selecting $descrip from another table in the database which correctly has this in the row as 16" Solid Steel Tube. When I try and copy this item into another table via my prepared statement that is when it wont insert properly.

Phil
  • 157,677
  • 23
  • 242
  • 245
RonnieT
  • 2,193
  • 4
  • 32
  • 43
  • add \ before " , e.g. 16\" – Satya Feb 06 '18 at 03:57
  • 1
    @Satya no, that should not be required – Phil Feb 06 '18 at 03:58
  • Yes, it is. You need to use PDO::quote() http://www.php.net/manual/de/pdo.quote.php – marekful Feb 06 '18 at 03:58
  • https://stackoverflow.com/a/3143705/1207049 – marekful Feb 06 '18 at 03:59
  • echo 'It can\'t happen.'; try like this – Bits Please Feb 06 '18 at 03:59
  • 1
    How are you verifying the issue? Where do you see the incorrect / truncated value? Are you absolutely sure that `$_POST['descrip'][$i]` contains the expected value – Phil Feb 06 '18 at 03:59
  • 2
    @marekful No, you most certainly do **not**! Also, OP is using _MySQLi_. A prepared statement with bound parameters should be able to accept any input value without special quoting or escaping – Phil Feb 06 '18 at 04:00
  • 2
    Could you all please stop suggesting escaping / special quoting. **IT IS NOT REQUIRED** – Phil Feb 06 '18 at 04:01
  • 1
    The most likely explanation for the observed behavior is that `$descrip` contains only the string `'16'`. The suggestions that we need to use PDO::quote, or add backslash before the double quote, or use mysqli_real_escape_string are **wrong**. None of those are needed. It's simple to setup a test that shows this. Just set `$descrip` to a literal value. e.g. `$descrip = '16" Test';` That will demonstrate that it is not the `bind_param` causing string to be truncated at the double quote. The problem is with the value being assigned to `$descrip`. – spencer7593 Feb 06 '18 at 04:34

1 Answers1

4

Based on your hint...

I am selecting $descrip from another table in the database which correctly has this in the row as '16" Solid Steel Tube'. When i try and copy this item into another table via my prepared statement that is when it wont insert properly.

I imagine you're using this value in a form like this (but maybe not exactly)

<input type="text" value="<?= $descrip ?>" name="descrip[]">

The issue here is that the rendered HTML looks like

<input type="text" value="16" Solid Steel Tube" name="descrip[]">

Notice the value attribute now has an end-quote after "16"?

You need to encode your values for safe use in HTML. Try...

<input type="text" value="<?= htmlspecialchars($descrip) ?>" name="descrip[]">

This will render like

<input type="text" value="16&quot; Solid Steel Tube" name="descrip[]">

Your PHP script will still receive it as the un-encoded string when submitted.

Phil
  • 157,677
  • 23
  • 242
  • 245
  • 1
    +10. Without a doubt, it's the value of `$descrip` that explains the observed behavior. OP erroneously concludes it's MySQL / prepared statement / bind_param that is causing the string to be truncated. – spencer7593 Feb 06 '18 at 04:39
  • @spencer7593 yup, that's why I asked OP to verify the value of `$_POST['descrip'][$i]` but after they added the extra information, I thought the problem seemed obvious. – Phil Feb 06 '18 at 04:41