Selecting certain row in mysql

Question

I am completely new to MYSQL and PHP, so i just need to do something very basic. I need to select a password from accounts where username = $_POST['username']... i couldn't figure this one out, i keep getting resource id(2) instead of the desired password for the entered account. I need to pass that mysql through a mysql query function and save the returned value in the variable $realpassword. Thanks!

EDIT: this code returned Resource id (2) instead of the real password CODE:

<?php
$con = mysql_connect('server', 'user', 'pass'); 
if (!$con) 
{ 
    die('Could not connect: ' . mysql_error()); 
} 
echo '<br/> '; 

// Create table
mysql_select_db("dbname", $con);

//Variables

//save the entered values

$enteredusername = $_POST['username'];
$hashedpassword = sha1($_POST['password']);

$sql = "SELECT password from accounts where username = '$enteredusername'";

$new = mysql_query($sql,$con);

echo "$new";


if (!mysql_query($sql,$con))
{
  die('Error: ' . mysql_error());
}



mysql_close($con);

?>

Probably a bit much for a starter, but you should consider SQL Injections and password hashing — juergen d, Feb 18 '13 at 18:35
here go to this website unityinvt.org and click the little log in button at the top right, type shadowpat as the username and test as the password, then it says resource id #2 instead of echoing the real password for that account, i want the real password for this account because once it sucessfully echos the real password i can check the entered pass against the real pass — Shadowpat, Feb 18 '13 at 18:41

score 1 · Answer 1 · answered Feb 18 '13 at 18:48

It will be a lot better if you use PDO together with prepared statements.

This is how you connect to a MySQL server:

$db = new PDO('mysql:host=example.com;port=3306;dbname=your_database', $mysql_user, $mysql_pass);

And this is how you select rows properly (using bindParam):

$stmt = $db->prepare('SELECT password FROM accounts WHERE username = ?;');
$stmt->bindParam(1, $enteredusername);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
$password = $result['password'];

Also, binding parameters, instead of putting them immediately into query string, protects you from SQL injection (which in your case would be very likely as you do not filter input in any way).

score 0 · Answer 2 · answered Feb 18 '13 at 18:47

By seeing this question we can understand you are very very new to programming.So i requesting you to go thru this link http://php.net/manual/en/function.mysql-fetch-assoc.php

I am adding comment to each line below

$sql = "SELECT id as userid, fullname, userstatus
        FROM   sometable
        WHERE  userstatus = 1"; // This is query

$result = mysql_query($sql); // This is how to execute query

if (!$result) { //if the query is not successfully executed
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) { // if the query is successfully executed, check how many rows it returned
    echo "No rows found, nothing to print so am exiting";
    exit;
} 

while ($row = mysql_fetch_assoc($result)) { //fetch the data from table as rows
    echo $row["userid"]; //echoing each column
    echo $row["fullname"];
    echo $row["userstatus"];
}

hope it helps

score 0 · Answer 3 · answered Feb 18 '13 at 18:48

try this

   <?php
  $con = mysql_connect('server', 'user', 'pass'); 
 if (!$con) 
 { 
 die('Could not connect: ' . mysql_error()); 
 } 
 echo '<br/> '; 

// Create table
 mysql_select_db("dbname", $con);

 //Variables

 //save the entered values

 $enteredusername = mysql_real_escape_string($_POST['username']);
 $hashedpassword = sha1($_POST['password']);

 $sql = "SELECT password from accounts where username = '$enteredusername'";

 $new = mysql_query($sql,$con);

 $row = mysql_fetch_array($new) ;
 echo $row['password'];

 if (!$new)
{
 die('Error: ' . mysql_error());
}



 mysql_close($con);

?>

Achrome · Accepted Answer · 2013-02-18T19:07:30.563

0

I think your code looks something like this

$realpassword = mysql_query("SELECT password 
     from accounts where username = '$_POST[username]'");
echo $realpassword;

This will return a Resource which is used to point to the records in the database. What you then need to do is fetch the row where the resource is pointing. So, you do this (Note that I am going to use structural MySQLi instead of MySQL, because MySQL is deprecated now.)

$connection = mysqli_connect("localhost", "your_mysql_username", 
    "your_mysql_password", "your_mysql_database") 
    or die("There was an error");
foreach($_POST as $key=>$val) //this code will sanitize your inputs.
    $_POST[$key] = mysqli_real_escape_string($connection, $val);
$result = mysqli_query($connection, "what_ever_my_query_is") 
    or die("There was an error");
//since you should only get one row here, I'm not going to loop over the result.
//However, if you are getting more than one rows, you might have to loop.
$dataRow = mysqli_fetch_array($result);
$realpassword = $dataRow['password'];
echo $realpassword;

So, this will take care of retrieving the password. But then you have more inherent problems. You are not sanitizing your inputs, and probably not even storing the hashed password in the database. If you are starting out in PHP and MySQL, you should really look into these things.

Edit : If you are only looking to create a login system, then you don't need to retrieve the password from the database. The query is pretty simple in that case.

$pass = sha1($_POST['Password']);
$selQ = "select * from accounts 
    where username = '$_POST[Username]' 
    and password = '$pass'";
$result = mysqli_query($connection, $selQ);
if(mysqli_num_rows($result) == 1) {
    //log the user in
}
else {
    //authentication failed
}

Logically speaking, the only way the user can log in is if the username and password both match. So, there will only be exactly 1 row for the username and password. That's exactly what we are checking here.

edited Feb 18 '13 at 19:07

answered Feb 18 '13 at 18:49

Achrome

7,273
14
32
44

Ok good news, i get the desired password printed to the screen but immediatly after the password i get Error:Query was empty making the result correctpassError:Query was empty. This code you posted was exactly what i needed, now how do i fix it to get rid of the Error. Is that error part of the variable $realpassword or something echoed to the screen by Mysql. Thanks! – Shadowpat Feb 18 '13 at 19:05
How would i make this a safer program? Thanks! – Shadowpat Feb 18 '13 at 19:06
@Shadowpat use mysqli or PDO as above, hash passwords using something secure like [password_hash()](http://php.net/password_hash) (see comments if you have PHP < 5.5). – Mike Feb 18 '13 at 19:08
I think @Shadowpat is already using SHA1 to store passwords. I've updated my post with a skeleton login system that you can check out. – Achrome Feb 18 '13 at 19:11
`SHA1` should *not* be used for password hashing – Mike Feb 18 '13 at 19:13
Ideally, yes. I personally prefer `MD5`. – Achrome Feb 18 '13 at 19:13
`MD5` with proper salting and multiple levels does prove pretty robust. Although still not as robust as `SHA3` – Achrome Feb 18 '13 at 19:16
I am not one, but I have heard several security experts say that MD5 should not be used for password hashing under any circumstances, even if you salt and stretch it. This is because MD5 is vulnerable to collisions. Currently the only two that should be used with PHP are bcrypt and PBKDF2 (and scrypt when it gets included into the source code circa PHP 5.7). I recommend you read http://crackstation.net/hashing-security.htm. And to the OP I recommend http://stackoverflow.com/questions/549. Good luck to both of you. – Mike Feb 18 '13 at 19:32
That's an interesting read. I have had some experience in Crypto, but not too much. I guess I did learn something new today. Thanks @Mike. – Achrome Feb 18 '13 at 19:41
Remove this : if (!$new) { die('Error: ' . mysql_error()); } – Achrome Feb 18 '13 at 21:42
Ok i did that an everything is working smoothly, thank you all! Now i have a few more questions, sorry... I need to let people sign up, which is fairly easy, i just need to add the info to the database/table, but i also need to know how to secure the content that is being sent through the internet. Do i hash it as its being added to the table? How should i make it secure, you have listed tons of examples... which is best? SHA1? MD5? bcrypt? PBKDF2? please submit an example thanks! – Shadowpat Feb 18 '13 at 23:49
Just use `SHA512`for hashing. – Achrome Feb 19 '13 at 00:05
ok can i have a link for a tutorial? Do i encrypt it when it is being created in the database or just when it is being signed in? – Shadowpat Feb 19 '13 at 00:10
if i use crypt() is it safe to leave it unsalted? – Shadowpat Feb 19 '13 at 00:30
could you please set up a salted crypt for me in this code: http://pastebin.com/8uVBVPX6 – Shadowpat Feb 19 '13 at 00:35
With SHA512, you would not need salting initially. You can implement it going ahead, as you get more knowledge and practice. – Achrome Feb 19 '13 at 00:36
ok could you set that up in the code on this link please: www.pastebin.com/8uVBVPX6 because i dont know the best way to do that – Shadowpat Feb 19 '13 at 00:49
`$hashedPassword = hash("sha512", $plainTextPassword);` – Achrome Feb 19 '13 at 00:50
please intigrate that hashing into my code and save it to paste bin. I cant figure out how to get it to get it to work... – Shadowpat Feb 19 '13 at 01:13

score -1 · Answer 5 · answered Feb 18 '13 at 18:37

-1

<?php
$query = "SELECT password_field_name FROM UsersTableName WHERE username_field_name =".$_POST['username'];
$result = mysql_query($query);
$row = mysql_fetch_array($result);
echo $row['password_field_name'];
?>

answered Feb 18 '13 at 18:37

Ehsan

2,165
7
32
66

score -1 · Answer 6 · answered Feb 18 '13 at 18:39

-1

$username = $_POST['username'];                                       
$login_query = "SELECT password FROM users_info WHERE users_info.username ='$username'";        
$password = mysql_result($result,0,'password');

answered Feb 18 '13 at 18:39

mariosk89

849
1
11
27

pffff, yeah, you're right. my mistake. but anyways, this is the main (and simplest) idea!!! – mariosk89 Mar 01 '13 at 07:44

Selecting certain row in mysql

6 Answers6