PHP MySQL Update Query Failing

Question

I'm trying to execute the following query to update a record in a table.

require "conn.php";

$user_name = $_POST["username"];
$code = $_POST["code"];
$name = $_POST["groupname"];

echo "$user_name, $code, $name";

$sql_update = "UPDATE users SET group = '$name' WHERE username = '$user_name'";

if ($conn->query($sql_update) === TRUE) {
echo "success";
} 
else {
echo "fail";
}

The query fails and I'm not sure why. The connection is made and I'm able to echo the username code and name. Is there a reason why it's not working?

What class is `$conn` an instance of? And, do you know that this code is vulnerable to SQL injection? — Peter Bailey, Aug 02 '16 at 21:42
usually, at least PDO's `->query` returns either `false` or a result set, but *never* `true`. So that might be the simple error here ... (I believe mysqli does the same). Also, in the failure case you could (for debugging purposes and on devel only) `echo $conn->error` (mysqli) or `print_r($conn->errorInfo())` (PDO). That said, **please use prepared statements!** — Jakumi, Aug 02 '16 at 21:47
$conn is another file in my public_html folder which just defines the database name, username, password, and server name. This is my first time using php and i'm not familiar with securing against injections. I don't know if I should be concerned about them if its a very basic app and I'm probably not going to publish it. — Arush Shah, Aug 03 '16 at 00:34

score 0 · Accepted Answer · edited Aug 03 '16 at 01:10

0

Your code is not secure

Look at this code with prepared statements

require_once("conn.php");
$user_name = $conn->real_escape_string($_POST["username"]);
$code = $conn->real_escape_string($_POST["code"]);
$name = $conn->real_escape_string($_POST["groupname"]);
$sql_update = $conn->prepare("update `users` set `group` = ? where `username` = ?");
$sql_update->bind_param("ss",$name,$user_name);
$sql_update->execute();
$sql_update->close();
$conn->close();

And conn.php file should be like this

$config = parse_ini_file('config.ini'); // Connection infos.
$conn = mysqli_connect('localhost',$config['username'],$config['password'],$config['db_name']);
if($conn === false) {
 die("Something was wrong ! Please try again later."); // Error if connection not ok.
} 
$conn->set_charset("utf8");

Create file outside the public_html folder named config.ini to write connection data

[db_connection]
username = username
password = password
db_name = dbname

This function binds the parameters to the SQL query and tells the database what the parameters are. The "sss" argument lists the types of data that the parameters are. The s character tells mysql that the parameter is a string.

The argument may be one of four types:

i - integer
d - double
s - string
b - BLOB

Learn more here

edited Aug 03 '16 at 01:10

RiggsFolly

93,638
21
103
149

answered Aug 02 '16 at 21:55

Mourad karoudi

58
1
6

1

Did you even test this code? You have a major syntax error in the query. Plus, the simple fix to the OP's code was to escape their column name, being a reserved MySQL word; that's the real problem. – Funk Forty Niner Aug 03 '16 at 00:51
Yes I test this code and it works What wrong in my code ?? – Mourad karoudi Aug 03 '16 at 00:55
1

Now you edited; so you saw/figured out what I meant then. – Funk Forty Niner Aug 03 '16 at 00:57
1

What is the point of `real_escape_string` if you are using parameterized bound values? – RiggsFolly Aug 03 '16 at 00:59
You're welcome. Edit: You deleted your "thank you" comment. – Funk Forty Niner Aug 03 '16 at 01:01
Yet, @RiggsFolly is right; you don't need that `real_escape_string` since you're using a prepared statement. – Funk Forty Niner Aug 03 '16 at 01:03
I use real escape string in any variables I use to make it more secure That's what I think :) – Mourad karoudi Aug 03 '16 at 01:04
Thank you, the real syntax error in my code was the reserved word "group". I put backticks around it and now it works. – Arush Shah Aug 03 '16 at 01:51
@ArushShah you should change and not use `group` as a column name. – Jay Blanchard Aug 03 '16 at 11:42

PHP MySQL Update Query Failing

1 Answers1