Getting UserID when posting to MySQL

Question

I'm looking to get other information when logging into my database. I'm looking to get the TechID of the tech that has successfully signed in and store it within "output" in the second section. Wondering if you could help.

PHP:

$myusername=$_POST['username'];
$mypassword=$_POST['password'];

$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);

$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT TechNo, TechName, TechUser,TechPass FROM $tbl_name 
WHERE TechUser='$myusername' and TechPass='$mypassword'";
$result=mysql_query($sql);

$count=mysql_num_rows($result);

if($count==1)
{
echo "***TechID goes here";
}
else
{
echo 'false';   
}

Post Method:

function checkEvents()
{
var username = $("#username").val();
var password = $("#pass").val();
$.post('checklogin.php', {username: username, password: password}, 
        function(output){
                if(output == 'false')
                {
                Win('#geteventslogin', 0);
                popupcetion('#loginfailed', 1);
                }
                else
                {
                Win('#geteventslogin', 0);
                alert(output); ///output = TechID number.
                popupcetion('#getevents', 1);
                }
        });
}

What im trying to do is display a list of jobs from another database, each tech is assigned jobs and i want only to display the correct jobs for that tech. This question has probably been asked before. If you could point me towards a post or answer my question, i would be much appreciative.

Thanks in advance.

What is your question? Also, there is a syntax error in your SQL statement. And are you seriously storing clear text passwords in that DB? — simbabque, Jan 26 '14 at 12:34
is the missing space in you statement a typo? DONT store plain text passwords in you database. Also, you should switch to mysqli_ or even better PDO functions instead of mysql_ — Gert B., Jan 26 '14 at 12:37
It was a typo, my bad. I'm using plain text password ATM just to i can fix this. could you poissibly point me in the right direction with both mysqli and how to encrypt the password?? — Aero204, Jan 26 '14 at 12:39
Apart from your code being vulnerable to [Unsecure Password](http://stackoverflow.com/a/2094713/2513523) and [SQL Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Is `TechNo` and `TechID` same? — AyB, Jan 26 '14 at 12:40
yes TechNo = TechID sorry. I did some reading to prevent SQL injection, should stripslashes and mysql_real_escape_string prevent this from happening?? I understand my password is plain, how would i go about fixing these security risks? — Aero204, Jan 26 '14 at 12:46
For your password use [crypt](http://php.net/crypt) to hash and store that value in the database. And yes `mysql_real_escape_string` does help prevent but since `mysql_*` functions are deprecated, you need to migrate to `mysqli_*` — AyB, Jan 26 '14 at 12:51
so i would add for example: "$hashpass = crypt($mypassword);" then when i do my sql query i would do : "$sql="SELECT TechNo, TechName, TechUser,TechPass FROM $tbl_name WHERE TechUser='$myusername' and TechPass='$hashpass'";" ?? — Aero204, Jan 26 '14 at 12:59
Yes, `crypt` is a one-way hash, which means you also need to `crypt()` the user submitted password to check with the database's one. Also you can use your own [`salt`](http://www.php.net/manual/en/faq.passwords.php#faq.passwords.salt) for more security. — AyB, Jan 26 '14 at 13:21

score 1 · Accepted Answer · answered Jan 26 '14 at 12:37

I think your code should look like this

$sql="SELECT TechID FROM $tbl_name 
WHERE TechUser='".$myusername."' and TechPass='".$mypassword."'";
$result=mysql_query($sql);

$count=mysql_num_rows($result);

if($count==1)
{
     $row = mysql_fetch_assoc($result);
     echo $row['TechID'];
}
else 
{
    echo 'false';   
 }

Hope it will help.

score 0 · Answer 2 · answered Jan 26 '14 at 13:26

I think you should take more care about stripslashes and echoing variables:

$myusername=$_POST['username'];
$mypassword=$_POST['password'];

$myusername = get_magic_quotes_gpc() ? stripslashes($myusername) : $myusername;
$mypassword = get_magic_quotes_gpc() ? stripslashes($mypassword) : $myusername;

$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT TechNo, TechName, TechUser,TechPass FROM $tbl_name 
WHERE TechUser='$myusername' and TechPass='$mypassword'";
$result=mysql_query($sql);

if( mysql_num_rows($result) )
{
    $fields = mysql_fetch_assoc($result);
    echo "TechID = ". htmlspecialchars($fields['TechNo']); // or 'TechID' !?
}
else
{
    echo 'false';   
}

;)

Getting UserID when posting to MySQL

2 Answers2