To fetch a field value on basis of user input

Question

I am trying to fetch a field value(SecurityQues) on basis of user input(username).

Following is the code:

$substr=substr($usrnm,0,2);

if($substr=="AC")
{
    $res="SELECT SecurityQues FROM reg_ac WHERE UserName=$usrnm";
}
else
{
    $res="SELECT SecurityQues FROM reg_indi WHERE UserName=$usrnm";
}       

$result = mysql_query($res,$db_handle); 

$result = mysql_query($res);

while($row = mysql_fetch_assoc($result))
{   

    echo $row['SecurityQues'];
}

But i am getting the following warning:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\my on line 120

score 1 · Answer 1 · edited May 23 '17 at 10:25

That error message is caused by the fact that your query has an error and fails to execute and you have no error checking in place to catch that.

Since username is a string, it needs to be inside quotation marks or else your query will keep on failing like it currently does.

$res="SELECT SecurityQues FROM reg_ac WHERE UserName='$usrnm'";
                                                     ^      ^

And don't execute your query two times. Although that is not causing your current error but that is just waste of resources and unnecessary .

Even after that fix, your query is so prone to injections it could bring down your mysql server before you can blink your eye.

How can I prevent SQL injection in PHP?

score 0 · Answer 2 · answered Oct 10 '14 at 17:19

Remove this one

$result = mysql_query($res);

And change this one

if($substr=="AC")
{
    $res="SELECT SecurityQues FROM reg_ac WHERE UserName=$usrnm";
}
else
{
    $res="SELECT SecurityQues FROM reg_indi WHERE UserName=$usrnm";
}

To this one

if ( $substr == "AC") $res = "SELECT SecurityQues FROM reg_ac WHERE UserName = '{$usrnm}'";
else $res = "SELECT SecurityQues FROM reg_indi WHERE UserName = '{$usrnm}'";

score 0 · Answer 3 · edited May 23 '17 at 12:11

Ignoring the fact that you are prone to SQL injection and that the original Mysql API is deprecated there is an error in your query as pointed out by Hanky Panky. At the line :

$result = mysql_query($res,$db_handle);

The result of the variable $result is initialized to the boolean false which is not a valid argument for mysql_fetch_assoc.

You can get more information on what is happening with something like this :

$result = mysql_query($res,$db_handle) or die ("Error in query: $query. ".mysql_error());

You should really consider using Mysqli and prepared statement to avoid SQL injection and something like this comic strip from happening.

Yoric · Answer 4 · 2014-10-10T17:30:38.403

this is not the best way to achieve this, but If i stick to your code, he is the correction (removing second mysql_query, prone to your error) :

$substr=substr($usrnm,0,2);

if($substr=="AC")
{
    $res="SELECT SecurityQues FROM reg_ac WHERE UserName='$usrnm'";
}
else
{
    $res="SELECT SecurityQues FROM reg_indi WHERE UserName='$usrnm'";
}

$result = mysql_query($res,$db_handle); 

while($row = mysql_fetch_assoc($result))
{   

    echo $row['SecurityQues'];
}

To fetch a field value on basis of user input

4 Answers4