Query Returns Success But no Rows Updated

Question

I'm currently having an issue where my query returns a success however there is no actual update that occurs when I check my SQL database. Oddly enough, when I copy the same exact query into phpMyAdmin, a response is successfully returned and the query works just fine, the rows are updated. (Note: I'm well aware of the high risk of SQL injection, however mysqli_escape_string isn't working for some reason so I'll worry about that when I go into the production stage.)

script.php

$fave = json_decode($_POST['af']);
$unfave = json_decode($_POST['uf']);
$fave = "'".implode("','", $fave)."'";
$unfave = "'".implode("','", $unfave)."'";
if ($fave !== "''"){
    $fq     = "UPDATE post SET fave='1' WHERE 'an_id' IN ($fave) AND bid='$bizusr' AND fave='0'";
    $r_fq   = mysqli_query($GLOBALS["___mysqli_ston"], $fq);
    $ar_fq  = mysqli_affected_rows($GLOBALS["___mysqli_ston"]);   
} else {
    $r_fq = 1;
    $ar_fq = 0;
}
if ($unfave !== "''"){
    $ufq    = "UPDATE post SET fave='0' WHERE 'an_id' IN ($unfave) AND bid='$bizusr' AND fave='1'";
    $r_ufq  = mysqli_query($GLOBALS["___mysqli_ston"], $ufq);
    $ar_ufq = mysqli_affected_rows($GLOBALS["___mysqli_ston"]);   
} else {
    $r_ufq = 1;
    $ar_ufq = 0;
}
if ($r_fq && $r_ufq){
    $output = json_encode(array('type'=>'error', 'text' => "Favourites have been updated successfully. You've added $ar_fq favorites and removed $ar_ufq favorites." ));
    die($output);
}
if (!$r_fq && $r_ufq){
    $output = json_encode(array('type'=>'error', 'text' => "We've successfully favorited $ar_fq links, however there was an issue in unfavoriting some links, try refreshing." ));
    die($output);
}
if ($r_fq && !$r_ufq){
    $output = json_encode(array('type'=>'error', 'text' => "We've successfully unfavorited $ar_ufq links, however there was an issue in favoriting some links, try refreshing." ));
    die($output);
}
if (!$r_fq && !$r_ufq){
    $output = json_encode(array('type'=>'error', 'text' => "There was an error in updating your favorited links." ));
    die($output);
}
//        $un = mysqli_prepare($GLOBALS["___mysqli_ston"], "UPDATE analytics SET fave='0' WHERE an_id IN (?) AND bid= ? AND fave='1'");
//        $fa = mysqli_prepare($GLOBALS["___mysqli_ston"], "UPDATE analytics SET fave='1' WHERE an_id IN (?) AND bid= ? AND fave='0'");
//        mysqli_stmt_bind_param($un, 'ss', $unfave, $blockject);
//        $a = mysqli_stmt_execute($un);
//        mysqli_stmt_close($un);
//        mysqli_stmt_bind_param($fa, 'ss', $fave, $blockject);
//        $b = mysqli_stmt_execute($fa);
//        mysqli_stmt_close($fa);

The variables $fave and $unfave would return values like so: 'abcd123','dcba321','hello123', which would make the query look like so:

UPDATE post SET fave='0' WHERE 'an_id' IN ('abcd123','dcba321','hello123') AND bid='$bizusr' AND fave='1';

Now, entering the query into phpMyAdmin works just fine, but when doing it through php, the response returns a success however no rows are actually updated, so I'm not sure what is going on as my php error.log is as clean as a whistle.

Also, if you're wondering what my require_once connection.php file looks like which connects me to the database, it is the following:

$link = ($GLOBALS["___mysqli_ston"] = mysqli_connect(DB_HOST,  DB_USER,  DB_PASSWORD));
if(!$link) {
    die('Failed to connect to server: ' . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)));
}

//Select database
$db = ((bool)mysqli_query($GLOBALS["___mysqli_ston"], "USE " . constant('DB_DATABASE')));
if(!$db) {
    die("Unable to select database");
}

add: echo $fq = "UPDATE post SET fave='1' WHERE 'an_id' IN ($fave) AND bid='$bizusr' AND fave='0'";exit(); and check what query is passing? — Vigneswaran S, Dec 01 '15 at 17:16
@VigneswaranS I've done that and it returns the update sql, I then copy that returned query into phpMyAdmin and it successfully updates the row — Jayke, Dec 01 '15 at 17:20
@Drew I do have a connection, otherwise there'd be a response that says that, but I've resolved my issue luckily, now I have to fix my mysqli_escape_string issue — Jayke, Dec 01 '15 at 17:22
as you are in `mysqli`, why not just finally get away from goofy string concats and do parameter binding once and for all with prepared statements? — Drew, Dec 01 '15 at 17:26
@Drew I actually have commented out parameter bindings, however they're commented out because they weren't working — Jayke, Dec 01 '15 at 17:31
look at [this answer](http://stackoverflow.com/a/33919501) about a third of the way down — Drew, Dec 01 '15 at 17:38
@Drew I've tried that and the error log returns 0 errors. I even get a true response when using the following code: `$ufq = mysqli_prepare($GLOBALS["___mysqli_ston"], "UPDATE post SET fave='0' WHERE an_id IN (?) AND bid='$blockject' AND fave='1'"); mysqli_stmt_bind_param($ufq, 's', $unfave); $r_ufq = mysqli_stmt_execute($ufq); $ar_ufq = mysqli_stmt_affected_rows($ufq);` However nothing is updated, mysqli_stmt_affected_rows always returns 0 as well. — Jayke, Dec 01 '15 at 18:14
Jayke, you can't bind on in clauses. It is a well known stipulation situation scenario :P — Drew, Dec 01 '15 at 18:40
my point was to do bindings on those things that you can bind on — Drew, Dec 01 '15 at 18:44
Ohhhh well then that explains why it wasn't working! Haha thank you for clearing that up. Not sure how to prevent sql injections via my current route then @Drew But I'll definitely use it for my other queries, thank you — Jayke, Dec 01 '15 at 19:04

score 0 · Answer 1 · answered Dec 01 '15 at 17:21

0

Silly me, I'm not sure why it was returning a successful query, however wrapping the column id an_id in single quotes was the issue

answered Dec 01 '15 at 17:21

Jayke

9
3

Because the SQL was still valid - but if you checked how many rows it had changed the value would have been 0. – symcbean Dec 01 '15 at 17:24

Query Returns Success But no Rows Updated

1 Answers1