PHP - MySQL select, from, where

Question

I am having some difficulties selecting a field in a row. Perhaps someone can point me into the correct direction to read some proper documentation, I am pretty new at this.

I have a table called users where I have a row with a username set to ryanjay. I am trying to select the password from this row.

This is what I have right now, and I am getting an error:

Unknown column 'password' in 'field list'

Here is my code

$loggin_user = $_COOKIE['username'];

$query = "SELECT password FROM users WHERE username = $loggin_user";
$result = mysql_query($query) or die($query."<br/><br/>".mysql_error());
$password = mysql_result($result, 0);
echo $password;

For the sake of this question, password is 'password'. When echoing $password, I should see 'password'. But nothing.

I am able to write to the database and everything. I am also including a db.php which has my database info for connecting.

Any help would be great.

I wish I could up-vote @pst's comment more than once. Please don't be put off by the tone of point #1. _Please_ take point #2 to heart (start by using `mysqli` instead of `mysql`, continue by escaping your input and/or using PDO or something like it for database interaction). *Definitely absolutely without hesitation* heed point #3. You should _never_ be able to retrieve a user's password. You can _reset_ it, but should never be able to _read_ it. — David, Mar 29 '13 at 23:31
User names (and passwords) must not be stored in cookies. The user enters those details, they're only required in that single request that logs the user in. — Arjan, Mar 29 '13 at 23:31
@David Thanks for the rewrite, your comment is worded much better - I need to settle down sometimes :D — , Mar 29 '13 at 23:34
Thanks for the comments guys. For now, I will continue to use mysql and usernames ONLY because this is for a test site to show some clients some basic ideas for a website. When it comes down to the actual site and paid word, it'll all be switched over and probably coded by someone else. — Ryan, Mar 29 '13 at 23:35

Will B. · Accepted Answer · 2013-03-29T23:30:42.587

4

try

$loggin_user = mysql_real_escape_string($_COOKIE['username']);
$query = "SELECT `password` FROM `users` WHERE `username` = '$loggin_user'";
$result = mysql_query($query) or die($query."<br/><br/>".mysql_error());
$password = mysql_result($result, 0);
echo $password;

edited Mar 29 '13 at 23:30

answered Mar 29 '13 at 23:25

Will B.

17,883
4
67
69

This is not it. It would be a syntax error if it was taken to be anything other than a column name at this junction. – Mar 29 '13 at 23:26
1

And in fact, PASSWORD is *not* a reserved word in MySQL. See http://dev.mysql.com/doc/refman/5.6/en/reserved-words.html – Bill Karwin Mar 29 '13 at 23:27
besides the login/loggin typo, this worked. I'll have to look up what mysql_real_escape_string does. Thank you – Ryan Mar 29 '13 at 23:29
It escapes out harmful characters to prevent some mysql injection techniques. You should look into switching to mysqli or PDO however. – Will B. Mar 29 '13 at 23:31
1

@fyrye Please read http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php?rq=1 - `mysql_real_escape_string` should not - that is **should not** - be used for general parameter sanitation. – Mar 29 '13 at 23:32
Thank fyrye. This site is currently just a test to show some clients, when it comes time to do the real site I will look into mysqli – Ryan Mar 29 '13 at 23:33
@Bill Karwin Thank you for the correction it is a function not a keyword, corrected the answer. I was aware of http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_password to which deemed it as reserved. – Will B. Mar 29 '13 at 23:35
@pst hence my suggestion to switch to mysqli and PDO, and the use of SOME. – Will B. Mar 29 '13 at 23:35

score 1 · Answer 2 · answered Mar 29 '13 at 23:25

1

It is telling you that there is no column called "password" in the table users.

answered Mar 29 '13 at 23:25

Captain Payalytic

1,061
8
9

it totally lies because I am looking at it right now.. hmm... – Ryan Mar 29 '13 at 23:27
@Ryan Did you *"save"* the table? Are you connected to the *same* database? From a CLI, use `DESCRIBE users` and see if the column is listed. If it is, copy and paste the results *to* the original question. If not, you've found the issue. – Mar 29 '13 at 23:29
yep, haven't made changes to the table in days. but when I went to edit password to rename it, it had a space at the end. would that of screwed it up? – Ryan Mar 29 '13 at 23:30
@Ryan I don't believe it would generate that error message (from the given code). – Mar 29 '13 at 23:31
weird... I was able to get it working with the above answer. thank you – Ryan Mar 29 '13 at 23:33

score 1 · Answer 3 · answered Mar 29 '13 at 23:26

You need single quotes ' for string columns. Otherwise SQL thinks, it's a table name. Try this:

$query = "SELECT `password` FROM `users` WHERE `username` = '".$loggin_user."' ";

Don't forget to escape your variables to prevent SQL injections. Also it's more pretty to use backticks in MySQL for table/column names to avoid problems with reserved names.

PHP - MySQL select, from, where

3 Answers3