php sql syntax error

[ccrandall]

I'm writing code to update 2 cells of an sql table and I'm getting a syntax error with the following line of code 

UPDATE afdt3235 SET 102='NdNicSg98NkM9cEZB7/SNQ==', 103='N' WHERE 104='42' 

I'm currently using: 

 

Server type: MariaDB
Server version: 10.1.22-MariaDB - mariadb.org binary distribution
Protocol version: 10

Apache/2.4.25 (Win32) OpenSSL/1.0.2j PHP/7.1.4
Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $

PHP version: 7.1.4

here is the entire error that I am getting:
Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '102='NdNicSg98NkM9cEZB7/SNQ==', 103='N' WHERE 104='42'' at line 1

 

[dodgeitorelse3]

104=42

[Barand]

https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

[ccrandall]

I have made a rather simple error that I did fix with the column names just being numbers, I fixed this by adding a letter before the numbers. now everything seems to go through correctly I don't get any errors but the database isn't updated. any suggestions?

[ccrandall]

$sql = "UPDATE ".$d203." SET u102='".$newPassword."', u103='N' WHERE 104=".$a104;

	print("<br> ".$sql."<br>");
	
if ($conn->query($sql) === TRUE) {
    print ("<script type='text/javascript'> alert('Your password has been updated'); </script>");
} else {
    echo "Error updating record: " . $conn->error;
	print ("<script type='text/javascript'> alert('Sorry something went wrong please try to change the password again'); </script>");
}


$conn->close();
	

here is the code to update the record but it keeps not updating even though it says it has and acts like it has

[mac_gyver]

the WHERE clause in the sql statement is probably FALSE, because you are still checking if the number 104 is equal to some value.

 

before you go any farther on your programming journey, don't use numbed or letter-numbed database columns or variables. you won't get very far, because people you are expecting to look at your code/queries won't waste their time trying to figure out what your code and queries are doing.

 

your code should be self-documenting. anyone reading a section of your code should be able to figure out what it is doing. database columns and variables should be named as to the meaning of the data in them.

 

your code is testing if the query execute without any errors. that doesn't mean that the query affected any row(s). if you want to specifically know if an row was updated, check the number of affected rows. if you are using the php mysqli extension, see the ->affected_rows property.

 

you should also be hashing passwords (see php's password_hash() and password_verify() functions) and you should not put data directly into sql query statements. use prepared queries when supplying data to an sql query statement.

[Phi11W]

$sql = "UPDATE ".$d203." SET u102='".$newPassword."', u103='N' WHERE 104=".$a104; 

Never use any value supplied by a user without validating it thoroughly first.

Obligatory XKCD Reference: Little Bobby Tables.

 

Never, ever store passwords in plain text. 

Generate a hash of the the user-supplied password and store/ compare against that. 

 

 

You appear to be using multiple tables (with the same structure) for multiple "things" (User groups?). 

That's generally a poor design choice and doesn't scale out half as well as you might think.  Better to use a single table and to use other methods (views/ application code) to keep different "communities" separate from one another. 

 

You appear to be using arbitrarily-generated table and column names. 

This is also, generally, a poor design choice, unless you have a lot of metadata elsewhere in your database/application that describes what all of these meaningless names ("u102", "u103", etc.) actually mean. 

I would expect you to have a "Users" table containing columns like "ID", "Name" and "PasswordHash".  If you're especially paranoid about values "bleeding" between user groups, then separate each into its own database. 

 

 

The SQL that you are building is a String values that just happens to be meaningful to your database engine. 

 

Always give yourself the opportunity to examine generated SQL before it gets submitted to the database.  During development, take the generated SQL, copy and paste it into your database tool of choice and make sure that it works.  As mac_gyver has already said, your WHERE clause ( "104=something" is never going to match anything (except the value 104, of course), so no rows will get updated. 

 

Regards,   Phill  W.

 

[ccrandall]

Than you Staff Alumni, that is what it was the fact I missed putting the u before 104. as for the hash and verification I didn't put those parts in the code that is on here because they work just fine and would compromise my site which has to be HIPPA compliant, I just showed the part that was relevant to the question and problem that I was having. I am still used to the old style MySQL not the new MySQLli format where I had to update the whole row.

 

Thank You Again Guys

Christopher J. Crandall