Home MySQL update fails when using encoded html
Reply: 0

MySQL update fails when using encoded html

Rob H
1#
Rob H Published in 2018-02-14 17:19:09Z

I have a database field that stores text content. The content includes html, which is encoded. I have written a search and replace tool that finds a term in the database field, gives it a context of 40 characters and then replaces that string with the replace term, also in context. I need the context because I need to decide case by case whether to replace the term, which might occur many times in the same field. Using the context narrows it down to that one instance.

Here is the problem: when there are encoded tags in the context, the replacement does not work. Specifically, the MySQL update of the replace string fails.

Here is an example of the search and replace terms:

<span style=display:none class=find>gt;&lt;body&gt;&lt;p&gt;&lt;strong&gt;A real estate Agent in Texas Cannot Represent No One.</span> <span style=display:none class=replace>gt;&lt;body&gt;&lt;p&gt;&lt;strong&gt;A rah1 Agent in Texas Cannot Represent No One.</span>

I pass these on via ajax to this php function where each becomes $search and $replace respectively (I am also passing on table, id and column info which you see used. That works):

include 'classes/index.php';
$q = new db;
extract($_POST);
$selected = $q->select_where($table, "id = {$id}", 'id');
$original_text = $selected[0][$col];
$changed_text = str_ireplace($search,$replace,$original_text);      
$updated = $q->update_row($table, array($col), array($changed_text), $id);
if ($updated) { 
  echo "Updated {$search} to {$replace}"; 
} else { 
  echo "it didn't work. Table: {$table}, Column: {$col}, ID: {$id} Changed Text: {$changed_text}"; 
}

Finally, here is the method I use for the update. I've used this in many contexts and it has been reliable. But maybe something about this scenario breaks it?

    function update_row($table, $columns, $data, $id) {
    // Prepare Variable
    $counter = 0;
    foreach ($columns as $column) {
        $prepare .= $column . ' = :' . $column . ', ';
    }
    $prepare = rtrim($prepare,', ');
    $prepare_query = "UPDATE {$table} SET {$prepare} WHERE id = :id";

    // Execute Variable
    $counter=0;
    foreach ($data as $datum) {
        $column_name = $columns[$counter];
        $update[":{$column_name}"] = stripslashes($datum);
        $counter++;
    }   
    $update[':id'] = $id;

    // Run it
    $stmt = $this->pdo->prepare($prepare_query);
    $stmt->execute($update);
    if ( $stmt->rowCount() == 1 ) {
        return true;
    }
}

Thanks in advance for your input.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.256366 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO