Can't update db with php command

Forum Moderators: open

Message Too Old, No Replies

Can't update db with php command

saggy

7:27 am on Apr 11, 2013 (gmt 0)

Hi, I'm building a password database that stores passwords as MD5 hashes and plain text.
The command:

UPDATE people SET password=(MD5('password')),decryptpass="password" WHERE firstname="John" AND surname="Smith";

works fine from the command line, but the identical line (in single quotes or double quotes) fails in my php script, with the message "Parse error: syntax error, unexpected T_STRING in E:\Server\htdocs\newpw.php on line 43".
Any ideas why?
Thanks

topr8

8:52 am on Apr 11, 2013 (gmt 0)

yes, there is a problem on line 43 of your script.

have you forgotten a ';' on a line somewhere just before that?

that is not a mySQL error it is a parsing error with your php

saggy

10:54 am on Apr 11, 2013 (gmt 0)

Hi topr8,
Thanks for the quick reply, I can't see any semi-colons missing.
The actual code is:
<code>
<?php
$pwd1 = $_POST["pwd1"];
$pwd2 = $_POST["pwd2"];
$user = $_POST["user"];
echo "Password 1 = ".$pwd1;
echo " ";
echo "Password 2 = ".$pwd2;
echo " ";
echo "User is :".$user;
echo " ";

$fullname = (explode(" ",$user,2));
$f_name = $fullname[0];
$s_name = $fullname[1];

if ($pwd1 == $pwd2)
{
dbconnect("localhost", "user", "pass", "database");

$query = 'UPDATE people SET password=(MD5('pass')),decryptpass="pass" WHERE firstname="John" AND surname="Smith"';
$result = mysql_query($query) or die("Could not change password");
}

if (empty($pwd1))
{
Die("Password cannot be empty");
}
</code>

All looks good to me with my (very) limited knowledge of these things!
Thanks
S

saggy

10:57 am on Apr 11, 2013 (gmt 0)

I was trying the code with fixed values to get it working, and going to substitute the variables once its doing as it should.

topr8

12:20 pm on Apr 11, 2013 (gmt 0)

is that all the code?

the error is around line 43 ... where is that?

saggy

6:09 am on Apr 12, 2013 (gmt 0)

Line 43 is the $query line:
$query = 'UPDATE people SET password=(MD5('pass')),decryptpass="pass" WHERE firstname="John" AND surname="Smith"';

The whole code is:


<?php session_start(); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>
NEW PW create
</title>
<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<?php include 'mylib.php'; ?>
</head>

<body>

<?php
$pwd1 = $_POST["pwd1"];
$pwd2 = $_POST["pwd2"];
$user = $_POST["user"];
echo "Password 1 = ".$pwd1;
echo "<br /><br />";
echo "Password 2 = ".$pwd2;
echo "<br /><br />";
echo "User is :".$user;
echo "<br /><br />";

$fullname = (explode(" ",$user,2));
$f_name = $fullname[0];
$s_name = $fullname[1];


if ($pwd1 == $pwd2)
{
dbconnect("localhost", "user", "pass", "dbname");


$query = 'UPDATE people SET password=(MD5('password')),decryptpass="password" WHERE firstname="John" AND surname="Smith"';
$result = mysql_query($query) or die("Could not change password");
}

if (empty($pwd1))
{
Die("Password cannot be empty");
}
?>

</body>
</html>

The 'dbconnect' function (in mylib.php) is:


function dbconnect($host, $user, $pass, $db)
{
mysql_connect("$host","$user","$pass") or die("Connection Failed");
mysql_select_db("$db") or die("Cannot Open Database");
echo "Database connected";
}

Hope this helps.
Thanks
saggy

topr8

8:31 am on Apr 12, 2013 (gmt 0)

MD5('password')

maybe you should try double quotes instead.

saggy

11:23 am on Apr 12, 2013 (gmt 0)

That's got rid of the error message, thanks! Trying it with 'proper' info (variables) still doesn't work though.
All the variables exist (I can echo out $f_name & $s_name, as well as the others), but I get "Could not change password" message.
Thanks for your time on this!

topr8

1:21 pm on Apr 12, 2013 (gmt 0)

i'm a bit confused - if you connect properly your code should echo 'Database Connected' to the page

is this happening

saggy

2:18 pm on Apr 12, 2013 (gmt 0)

Yes, it comes back with all the echos, then 'Database Connected' immediately before 'Could not change password.

topr8

2:49 pm on Apr 12, 2013 (gmt 0)

has that user got permission to update tables,
what field type are the password fields and do they match the data you are trying to update them with.

OT, why are you storing the decrypted version of the password in the database?

saggy

5:10 pm on Apr 12, 2013 (gmt 0)

As far as I can remember (I'm at home now, the db is at work) the user permissions are set up properly. The MD5 field is VARCHAR(32) and the non-encrypted field is VARCHAR(40).
I'll remove the non-encrypted field when the db goes into full use, unless there's a way of hiding it?

saggy

5:56 am on Apr 16, 2013 (gmt 0)

Can confirm that the MD5 field is as I said above, the non-encrypted is VARCHAR(64).

topr8

8:31 am on Apr 16, 2013 (gmt 0)

echo out: $query

and see what that gets you.

...can you do the update using MySQL workbench or running the query in phpMyAdmin or whatever tool you use.

saggy

2:08 pm on Apr 16, 2013 (gmt 0)

I'm able to update the db using phpMyAdmin or command line with no problems.
Putting 'echo $query;' in between the line that defines $query and the $result line comes back with: Database connectedUPDATE people SET `password`=(MD5("word")),`decryptpass`="word" WHERE `firstname`=$f_name AND `surname`=$s_nameCould not change password

topr8

10:44 pm on Apr 16, 2013 (gmt 0)

well the variables should be actually values, and i suspect they should be in quotes.

like: `firstname`="example" AND `surname`="example"

saggy

7:02 am on Apr 17, 2013 (gmt 0)

Putting the variable names in double quotes (`firstname`="$f_name" AND etc..), still comes back with the same message as above. Putting real values in (`firstname`="John" AND etc..) comes back with "Database connected" followed by the echoed query, NO message saying it couldn't change the password, followed by "Total Rows updated: 0", with no updates done to the db.
btw I do really appreciate your thoughts on this matter.

topr8

7:14 am on Apr 17, 2013 (gmt 0)

are you sure you are setting the variables right?

is there a John Smith in the database? if not then that's why no updates were done with real values.

try using real values that actually exist in the database, what happens then? don't forget to actually chose a different password.

saggy

7:30 am on Apr 17, 2013 (gmt 0)

I am definitely using a name that does exist in the database, and using a fixed value in the password fields (different to the one its already set to).

topr8

7:33 am on Apr 17, 2013 (gmt 0)

also why don't you write the code you are actually using here, i assume the passwird is a variable too, not the word password.

the fact that the query is run fine (although without an update probably becuase you do not have a John Smith in the database, or you didn't actually change the password to a different one) shows that it works ... and that somehow you are not setting the variables or writign the code correctly when you are trying to use variables.

saggy

7:46 am on Apr 17, 2013 (gmt 0)

This is whole thing:


<?php session_start(); ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>
NEW PW create
</title>
<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<!--<link rel="stylesheet" href="style.css" />-->
<link rel="stylesheet" href="./includes/jquery-ui.css" />
<?php include 'mylib.php'; ?>
</head>

<body>

<?php
$pwd1 = $_POST["pwd1"];
$pwd2 = $_POST["pwd2"];
$user = $_POST["user"];
echo "Password 1 = ".$pwd1;
echo "<br /><br />";
echo "Password 2 = ".$pwd2;
echo "<br /><br />";
echo "User is :".$user;
echo "<br /><br />";

$fullname = (explode(" ",$user,2));
$f_name = $fullname[0];
$s_name = $fullname[1];

echo "First name = ".$f_name;
echo "<br /><br />";
echo "Surname = ".$s_name;
echo "<br /><br />";

if ($pwd1 == $pwd2)
{
dbconnect("localhost", "ecruser", "ecrpass", "ecrtrial");


$query = 'UPDATE people SET `password`=(MD5("word")),`decryptpass`="word" WHERE `firstname`="John" AND `surname`="Smith"';
echo $query;
$result = mysql_query($query) or die("Could not change password");

}

if (empty($pwd1))
{
Die("Password cannot be empty");
}

echo "<br /><br />";
echo "Total Rows updated: ".mysql_affected_rows();
?>

</body>
</html>

with 'hardwired' values for the password and the name.

topr8

8:39 am on Apr 17, 2013 (gmt 0)

... dupe

[edited by: topr8 at 8:41 am (utc) on Apr 17, 2013]

topr8

8:40 am on Apr 17, 2013 (gmt 0)

oh sorry, before for some reason i thought you were doign an update of someone already in the database.

topr8

8:45 am on Apr 17, 2013 (gmt 0)

not enough coffe! you are doign an update:

yeah but does that work with the hardwired values, like i asked before, i know it connects to the database and runs the query, but it doesn't do the update ...

is there a user called John Smith in the database? if not that's why you got the no rows updated message before.

but if that works even with no rows updated (eg it connects but there was nothing to update) then you are not insertign or settign the variables correctly.)

as i said use the code including all variables and echo out:
' '.$query.' ';
and see what you have ...
echoing it out with the hard coded values is a waste of time you know that works.

you need to enter a test user into your database (you have got test users in there right? POST the two passwords and the user name to the page and see what happens)

saggy

10:02 am on Apr 17, 2013 (gmt 0)

John Smith is the test user in the db.
With all the variables in single quotes, I get the "Unexpected T_VARIABLE" error, with all the variables in double quotes, I get no error message, but no update to the db, with the variables with no quotes, I get 'Could not change password', and I even tried the variables in backticks, which gave me the same as no quotes.

topr8

12:30 pm on Apr 17, 2013 (gmt 0)

personally i'd do this:

$query = 'UPDATE people SET password=(MD5("'.$pwd1.'")),decryptpass="'.$pwd1.'" WHERE firstname="'.$f_name.'" AND surname="'.$_name.'"';
echo ' '.$query.' ';

this will actually echo out the query you are sending to the database
if that looks correct, then you have a permissions problem, or the fields are expecting a different data type or whatever. what does it actually echo out?

here's what is happening at the moment with your permatations...

the "Unexpected T_VARIABLE" error: you've not used the quotes properly.

no error message, but no update to the db: the query is being sent to the db and is being run, however it doesn't do anything (maybe you haven't entered a different password or you are tying to enter invalid data or you don't have permission to do it)

I get 'Could not change password: this is your own error message which is written to the page because the query is malformed and did not run properly

saggy

4:16 pm on Apr 17, 2013 (gmt 0)

Give that man a medal as big as a frying-pan!
That sorted it! Just what are the rules for using single and double quotes. I get the impression there are conflicting opinions on the web about this.
Many thanks for your help.

topr8

4:35 pm on Apr 17, 2013 (gmt 0)

i don't need a medal, you need to learn some basics!

you've seen what works and how confused you got doing it the other way, maybe that's how you should handle quotes in the future, that way works for me anyway.

... php is not my thing really, perhaps you should ask the quotes question in the php forum.

although if you are learning and just starting out ... here is my advise to you - it makes codign much more long winded but it will make it much more secure.

1. ensure you check all POST/GET data is exactly as it should be, eg a string of a certain length etc. and whatever characters are allowed/not allowed - write functions to test for this.

2. do not actually write queries as you have done here, bind parameters to prepared statements ... [php.net...]

3. if you don't have a very good reason to use xhtml, and most people don't then don't use it, use regular html.

if you don't do 1 and 2 then it is only a matter of time before you are hacked.

saggy

5:48 pm on Apr 17, 2013 (gmt 0)

Thanks again for the good sound advice. I realise I've got a long way to go, but I did only start looking at php/mysql in earnest just before Christmas.
At present, I'm not overly concerned about security as any apps I develop will only be used locally, not on the internet, but I take your point.