MySqli prepared statement with $_POST from XMLHttpRequest isn't working

Question

I'm trying to get a simple mysqli prepared statement to work. I'm running this script using an XMLHttpRequest.

On the website I'm working on you'll be able to give a bunch of inputs and I need to sanitize everything. Since mysqli_real_escape_string is not sufficient I have to use prepared statements.

This is the query without prepared statement:

$account = $_POST["passaccountname"];
$newFullName = $_POST["userNameUpdate"];

$mysqli = new mysqli('localhost', 'root', '', 'os_test');
$sql = $mysqli->query("UPDATE test_users SET FULLNAME='".$newFullName."' WHERE LOWER(REPLACE(NAME, ' ', ''))='".$account."'");

This worked perfectly but of course this is not secure!

So I looked into how to make a prepared statement and it didn't look to hard. I changed the passaccountname variable around and match it to the 'NAME' column/row.

Even though on the surface it seemed simple nothing seems to happen, this is the code now:

$account = $_POST["passaccountname"];
$newFullName = $_POST["userNameUpdate"];

$mysqli = new mysqli('localhost', 'root', '', 'os_test');
$sql = $mysqli->query("UPDATE test_users SET FULLNAME=? WHERE NAME=?");
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ss", $n, $a);

$n = $newFullName;
$a = $account;
$stmt->execute();

Is there something wrong with my code? I really do not understand what I'm doing wrong here.

Answer 1

You're almost there.

Currently you're trying to execute this query instead of prepare it:

$sql = $mysqli->query("UPDATE test_users SET FULLNAME=? WHERE NAME=?");

Remove the call to query(). You could just replace that line with:

$sql = "UPDATE test_users SET FULLNAME=? WHERE NAME=?";

Since you pass that variable to prepare() on the next line.

---

Edit: In response to a comment below, it's also generally a good idea to enable mysqli exceptions in your code. By default errors like this one "fail silently", which can make them difficult to catch. (This was likely just for backward compatability with previous mysql/mysqli error checking.)

By enabling these exceptions, these errors are more visibly reported and easier to find.