Str_Replace in SQL Update

str_replace in SQL UPDATE?

T-SQL:

update TBL 
   set picture = Replace(picture, 'servera', 'serverb') 
 where picture like '%servera%'

Oracle:

update TBL 
   set picture = replace(picture, 'servera', 'serverb') 
 where picture like '%servera%'

MySQL:

update TBL 
   set picture = REPLACE(picture, 'servera', 'serverb') 
 where picture like '%servera%'

Update a column value, replacing part of a string

UPDATE urls
SET url = REPLACE(url, 'domain1.example/images/', 'domain2.example/otherfolder/')

str_replace and SQL Server queries with PHP

Yes, you can use the str_replace function:

str_replace("'", "''", "Neil O'Brien")

The example code you posted would look like:

$dbTABLE = "Table_Name";
$query_sql = sprintf("UPDATE [%s] SET DataText = '%s' WHERE PageID = %d",
$dbTABLE,
str_replace("'", "''", $PageHTML),
$PageID);

I suggest using some kind of library instead of building queries yourself.

str_replace with variable generated string not working with php

Instead of:

if ($data1 != '') {
    $col1 = "col1 = '".$data1."', "
}
if ($data2 != '') {
    $col2 = "col2 = '".$data2."', "
}
if ($data3 != '') {
    $col3 = "col3 = '".$data3."', "
}

use something like:

$update_arr = array();
if ($data1 != '') {
    $update_arr[] = "col1 = '".$data1."'";
}
if ($data2 != '') {
    $update_arr[] = "col2 = '".$data2."'";
}
if ($data3 != '') {
    $update_arr[] = "col3 = '".$data3."'";
}

and then create update part of query like this:

if ($update_arr) {
    $sql = "UPDATE table SET ".implode(", ", $update_arr)." WHERE ID = '$ID'";
}

Using str_replace in table query in MYSQL

You want to replace "cat='".$cat."'" with "cat='adventure'", not "cat=".$cat with "cat=adventure".

(Though you are inconsistent in saying if there are spaces around the =.)

But you should not do this and should use a placeholder instead.

MySql - Way to update portion of a string?

I think this should work:

UPDATE table
SET field = REPLACE(field, 'string', 'anothervalue')
WHERE field LIKE '%string%';

Use sql query in str_replace

Does it have security issue?

In the unlikely event that your $post object gets replaced with something else (and at that point I'd consider the website's security as already compromised), the attacker could replace the value returned by $post->ID with a malicious query string (a.k.a. SQL Injection).

To avoid that, as everyone else already pointed out, you should escape your query using the prepare() method from the $wpdb object:

$mycontent = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT `meta_value` FROM `wp_postmeta` WHERE `post_id` = %d AND `meta_key` = %s;",
        array( $post->ID, 'my_seo_title' )
    )
);

Out of curiosity, why are you manually retrieving the meta value from the database when we already have the get_post_meta() function (which does the whole security check automagically for you)? I mean, you could replace your code with:

$mycontent = get_post_meta( $post->ID, 'my_seo_title', true );

... and forget about writing queries by hand and/or making them secure (when not necessary).

---

Related Topics