MySQL Update Table using Variables?

Question

The background to this is just I am working on an page where I need to allow updates to table inside a database. However, the table and the values passed to it need to be dynamic due to there being 3 different tables, testing1, testing2 and testing3.

The variables pass to PHP script just fine and when I use echo I see the values I want. But when I try and conduct an Update query using these variables it fails and I can't figure out why.

Can somebody have a look at my query and hopefully point me in the right direction?

$thetable = "testing1";
        $currenttitle = htmlentities($_GET['currenttitle']);
        $newtitle = htmlentities($_GET['newtitle']);
        $newdesc = htmlentities($_GET['newdesc']);

        echo $currenttitle;
        echo $newtitle;

        $db = mysqli_connect($servername, $user, $password);

        if (!$db)
            {
                echo"NO CONNECTION AVAILABLE";
                exit();
            }

        mysqli_select_db ($db, "testing");

        $query ="UPDATE $thetable SET TITLE= $newtitle WHERE TITLE = $currenttitle";

        echo $query;

        $results = mysqli_query($db, $query);

        if(!$results)
            {
                echo"not working";
                exit();
            }

        echo"updated";

What i expected was for it to update the row of the table where TITLE is = the value of the variable but it returns no results.

Take advantage of [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php). **This will take care of any pesky quoting issues that may occur.** — aynber, Apr 09 '19 at 15:36
Instead of just saying "not working", check for the actual [mysqli errors](http://php.net/manual/en/mysqli.error.php) to find out why it failed. — aynber, Apr 09 '19 at 15:36

score 0 · Answer 1 · answered Apr 09 '19 at 15:41

You issue could be related to the assignement of string inside the query
but you should not use php var because you are at risk for sqlinjection

instead you should use prepapred sataemnet and bindig param

Do the the fact you have a controlled value (in this case a literal string) for $thetable then this is not involved in sqlinject
you could use

  $stmt = $db->prepare("UPDATE '" .$thetable ." SET TITLE= ? 
             WHERE TITLE = ? " );
  $stmt->bind_param('ss', $newtitle, $currenttitle); 

  $currenttitle = htmlentities($_GET['currenttitle']);
  $newtitle = htmlentities($_GET['newtitle']);

  mysqli_stmt_execute($stmt);

MySQL Update Table using Variables?

1 Answers1