-1

UPDATE: So I spent the day reading various posts on SQL injection and parameterized queries. I've come up with something that works and I think it's a reasonable update to my approach.

    $query = "UPDATE message_bundles SET bndName = ?, bndTagId = ?, bndSequence = ?, bndKeyboardArr = ? WHERE id = ?";

    $stmt = mysqli_prepare($connection, $query);
    mysqli_stmt_bind_param($stmt, 'siisi', $bnd_name, $bnd_tag_id, $bnd_sequence, $bnd_keyboard_arr, $id);
    $result = mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);

But, I still have the issue described in this original question. When I echo out the data, it's still breaking with the very first single quote it encounters; whether I use htmlspecialchars or htmlentities. Additional comments would be appreciated.

ORIGINAL: Normally when I save strings to MYSQL in PHP I run a function (below) that escapes quotes. I am doing the same thing but just after I serialize an array of strings before inserting into MYSQL. But when I retrieve the array and unsearialize it, only the first string with a single quote echo's inside an input field. Then the rest of the strings in the array won't echo.

Here is my loop through post to build array (note that the 3 values being added to an array are 1) string, 2) int, 3) string; maybe this is where my issue is):

foreach ($bnd_keyboard as $key) {
    if ($key['keyboard']) {
        $keyboard = $key['keyboard'];
        $target_bundle = $key['targetBundle'];
        $code_execute = $key['codeExecute'];
        $bnd_keyboard_arr["keyboards"][$keyboard] = array(
                "targetBundle" => $target_bundle,
                "codeExecute" => $code_execute          
            );
    }   
}

Then my function to escape quotes (note that it's an older function that might need to be updated, but hasn't caused me any issues until now):

function mysqli_prep($value) {
  global $connection;
  $magic_quotes_active = get_magic_quotes_gpc();
  $new_enough_php = function_exists("mysqli_real_escape_string"); // i.e. PHP >= v4.3.0
  if($new_enough_php) { // PHP v4.3.0 or higher
    // undo any magic quote effects so mysql_real_escape_string can do the work
    if($magic_quotes_active) {$value = stripslashes($value); }
    $value = mysqli_real_escape_string($connection, $value);
  } else { // before PHP v4.3.0
    // if magic quotes aren't already on then add slashes manually
    if(!$magic_quotes_active) {$value = addslashes($value); }
    // if magic quotes are active, then the slashes already exist
  }
  return $value;
}

Then I run this function on the array and serialize it:

$bnd_keyboard_arr = mysqli_prep(serialize($bnd_keyboard_arr));

Serialized data before insert into MYSQL looks like this:

a:1:{s:9:\"keyboards\";a:1:{s:5:\"aaa\'s\";a:2:{s:12:\"targetBundle\";s:2:\"93\";s:11:\"codeExecute\";s:3:\"aaa\";}}}

When I go to retrieve the data, unserialize it and echo into my page, If the 1st field (which is actually the KEY for an array within the array) has a quote, then it echo's ok, but then the next 2 values break and won't echo (either in a normal text echo, or within an input field).

If none of the 3 values have single quotes, then all 3 echo out properly inside the input's.

If the 3rd value has a single quote, then all three echo fine. Basically when my page encounters a single quote, even after htmlentites or htmlspecialchars is used, it breaks the rest of the values being echoed from the array.

I'm really stumped.

Jeff Solomon
  • 459
  • 6
  • 21
  • 6
    Why did you make a function to sanitize data when you could have used parameterized queries and saved yourself trouble and this whole mess? – John Conde Apr 21 '16 at 18:02
  • 5
    don't bother checking for magic_quotes. if you're using an old enough PHP that magic quotes is enabled, or even just available, then you're running on a stoneage/obsolete/buggy/crappy version. And note that `addslashes()` is utterly USELESS for sql injection prevention. – Marc B Apr 21 '16 at 18:04
  • 2
    possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/q/60174/) – Funk Forty Niner Apr 21 '16 at 18:06
  • 2
    Probably duplicate of [a little knowledge is a bad thing](http://www.phrases.org.uk/meanings/a-little-knowledge-is-a-dangerous-thing.html) – RiggsFolly Apr 21 '16 at 18:12
  • I realize that function is old, I didn't write it, I just haven't updated. I'm using the latest PHP version, so I'd like to get rid of that. @john conde how should I sanitize. – Jeff Solomon Apr 21 '16 at 19:06
  • BTW, it sucks to be a JV programmer on here. You guys hammer us for asking questions while we're learning. @riggsFolly thanks for the condescending comment. – Jeff Solomon Apr 21 '16 at 19:07
  • 1
    We have to have a little fun while we are here as well you know., but it did get 2 UV's for some reason, so it must have hit a chord with 2 others – RiggsFolly Apr 21 '16 at 19:26
  • In reading this post [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) I realize I am kinda operating on really outdated knowledge. But that post has many different approaches so it's hard to tell which to go with. I'm down to do the work to upgrade my approach, but it's hard to tell what the "catch-all" setup will be. – Jeff Solomon Apr 21 '16 at 19:51

1 Answers1

0

Well, I solved it. Good exercise for me though b/c it forced me to upgrade the way I send queries to MYSQL. I think that's way upgraded, although it turns out that had nothing to do with my original issue.

The problem, stupid as it always is, was this:

I was creating a htmlspecialchars string just fine like:

$keyboard = htmlspecialchars($keyboard, ENT_QUOTES);

Then I was grabbing a value from an array, except that used my $keyboard value like this:

$keyboard_arr = $bnd_keyboard_arr['keyboards'][$keyboard];

So of course if I was converting the $keyboard string using htmlspecialchars prior to inserting that string in my array key, well, it would break $keyboard_arr

The solution was to just move the $keyboard_arr = $bnd_keyboard_arr['keyboards'][$keyboard]; to one line after the $keyboard_arr = $bnd_keyboard_arr['keyboards'][$keyboard];

Yup. Like I said, JV.

Jeff Solomon
  • 459
  • 6
  • 21