is this a bug?

Am in the initial stages of development and am receiving the oddest error.
The field/column names in this particular table (users) are: fname, lname,
password, level, paid, (and userid).

When I run the following SQL statement from the command line it works fine:

SELECT level FROM users WHERE fname = [firstname] AND lname = [lastname] AND
password = [password] AND paid = 1;

Yet, when the exact same statement is within a PHP function receiving the
values as arguments such as:

function userLevel ($f_name, $l_name, $pw, &$userlevel) {
[connect to database, etc.]
$sql='SELECT level FROM users WHERE fname = ' . $f_name . ' AND lname = '
.. $l_name . ' AND password = ' . $pw . ' AND paid = 1';

ending with:
$userlevel=intval($sql);
return true;
}

....I get error #1054: Unknown column 'Joe' in 'where clause'... where "Joe"
represents the value in $f_name.

I've restructured the statement several times but with no success. Is this a
bug in MySQL ?

Re: is this a bug?

joeblow wrote:
> Am in the initial stages of development and am receiving the oddest error.
> The field/column names in this particular table (users) are: fname, lname,
> password, level, paid, (and userid).
>
> When I run the following SQL statement from the command line it works fine:
>
> SELECT level FROM users WHERE fname = [firstname] AND lname = [lastname] AND
> password = [password] AND paid = 1;
>
> Yet, when the exact same statement is within a PHP function receiving the
> values as arguments such as:
>
> function userLevel ($f_name, $l_name, $pw, &$userlevel) {
> [connect to database, etc.]
> $sql='SELECT level FROM users WHERE fname = ' . $f_name . ' AND lname = '
> . $l_name . ' AND password = ' . $pw . ' AND paid = 1';
>
> ending with:
> $userlevel=intval($sql);
> return true;
> }
>
> ...I get error #1054: Unknown column 'Joe' in 'where clause'... where "Joe"
> represents the value in $f_name.
>
> I've restructured the statement several times but with no success. Is this a
> bug in MySQL ?

No, not a bug in MySQL but in your PHP script

String in SQL should be single-quoted


$sql='SELECT level FROM users WHERE fname = \'' . $f_name . '\' AND lname =
\'' . $l_name . '\' AND password = \'' . $pw . '\' AND paid = 1';


To make it easier to see, we use double-quotes for the same $sql

$sql="SELECT level FROM users WHERE fname = '" . $f_name . "' AND lname = '" .
$l_name . "' AND password = '" . $pw . "' AND paid = 1";


String that aren't quoted are considered as column names.


//Aho

Re: is this a bug?

Wonderful!! Thank you very much!

"J.O. Aho" wrote in message
news:44m4v3F2qm0mU1@individual.net...
> joeblow wrote:
> > Am in the initial stages of development and am receiving the oddest
error.
> > The field/column names in this particular table (users) are: fname,
lname,
> > password, level, paid, (and userid).
> >
> > When I run the following SQL statement from the command line it works
fine:
> >
> > SELECT level FROM users WHERE fname = [firstname] AND lname = [lastname]
AND
> > password = [password] AND paid = 1;
> >
> > Yet, when the exact same statement is within a PHP function receiving
the
> > values as arguments such as:
> >
> > function userLevel ($f_name, $l_name, $pw, &$userlevel) {
> > [connect to database, etc.]
> > $sql='SELECT level FROM users WHERE fname = ' . $f_name . ' AND lname
= '
> > . $l_name . ' AND password = ' . $pw . ' AND paid = 1';
> >
> > ending with:
> > $userlevel=intval($sql);
> > return true;
> > }
> >
> > ...I get error #1054: Unknown column 'Joe' in 'where clause'... where
"Joe"
> > represents the value in $f_name.
> >
> > I've restructured the statement several times but with no success. Is
this a
> > bug in MySQL ?
>
> No, not a bug in MySQL but in your PHP script
>
> String in SQL should be single-quoted
>
>
> $sql='SELECT level FROM users WHERE fname = \'' . $f_name . '\' AND lname
=
> \'' . $l_name . '\' AND password = \'' . $pw . '\' AND paid = 1';
>
>
> To make it easier to see, we use double-quotes for the same $sql
>
> $sql="SELECT level FROM users WHERE fname = '" . $f_name . "' AND lname =
'" .
> $l_name . "' AND password = '" . $pw . "' AND paid = 1";
>
>
> String that aren't quoted are considered as column names.
>
>
> //Aho

Re: is this a bug?

I've made the same mistake in the past. Very confusing.
T.

joeblow wrote:
> Wonderful!! Thank you very much!
>
> "J.O. Aho" wrote in message
> news:44m4v3F2qm0mU1@individual.net...
>
>>joeblow wrote:
>>
>>>Am in the initial stages of development and am receiving the oddest
>
> error.
>
>>>The field/column names in this particular table (users) are: fname,
>
> lname,
>
>>>password, level, paid, (and userid).
>>>
>>>When I run the following SQL statement from the command line it works
>
> fine:
>
>>>SELECT level FROM users WHERE fname = [firstname] AND lname = [lastname]
>
> AND
>
>>>password = [password] AND paid = 1;
>>>
>>>Yet, when the exact same statement is within a PHP function receiving
>
> the
>
>>>values as arguments such as:
>>>
>>>function userLevel ($f_name, $l_name, $pw, &$userlevel) {
>>>[connect to database, etc.]
>>> $sql='SELECT level FROM users WHERE fname = ' . $f_name . ' AND lname
>
> = '
>
>>>. $l_name . ' AND password = ' . $pw . ' AND paid = 1';
>>>
>>> ending with:
>>> $userlevel=intval($sql);
>>> return true;
>>>}
>>>
>>>...I get error #1054: Unknown column 'Joe' in 'where clause'... where
>
> "Joe"
>
>>>represents the value in $f_name.
>>>
>>>I've restructured the statement several times but with no success. Is
>
> this a
>
>>>bug in MySQL ?
>>
>>No, not a bug in MySQL but in your PHP script
>>
>>String in SQL should be single-quoted
>>
>>
>>$sql='SELECT level FROM users WHERE fname = \'' . $f_name . '\' AND lname
>
> =
>
>>\'' . $l_name . '\' AND password = \'' . $pw . '\' AND paid = 1';
>>
>>
>>To make it easier to see, we use double-quotes for the same $sql
>>
>>$sql="SELECT level FROM users WHERE fname = '" . $f_name . "' AND lname =
>
> '" .
>
>>$l_name . "' AND password = '" . $pw . "' AND paid = 1";
>>
>>
>>String that aren't quoted are considered as column names.
>>
>>
>> //Aho
>
>
>

Re: is this a bug?

"joeblow" ha scritto nel messaggio
news:11ubktffhs67o5e@corp.supernews.com...
> Am in the initial stages of development and am receiving the oddest error.
> The field/column names in this particular table (users) are: fname, lname,
> password, level, paid, (and userid).
>
> When I run the following SQL statement from the command line it works
fine:
>
> SELECT level FROM users WHERE fname = [firstname] AND lname = [lastname]
AND
> password = [password] AND paid = 1;
>

this example is bad code (SQL INJECTION)

user == other user
password == password

use this
table
id (int autoincrement)
user (unique)
password = md5(pasword.token)
.......

and this query

select password from tabel where user=$_post['user']

if(md5($_POST['password'].token)===$row['password']){
echo " login ok"
}else{
echo "ko";
}



> Yet, when the exact same statement is within a PHP function receiving the
> values as arguments such as:
>
> function userLevel ($f_name, $l_name, $pw, &$userlevel) {
> [connect to database, etc.]
> $sql='SELECT level FROM users WHERE fname = ' . $f_name . ' AND lname =
'
> . $l_name . ' AND password = ' . $pw . ' AND paid = 1';
>
> ending with:
> $userlevel=intval($sql);
> return true;
> }
>
> ...I get error #1054: Unknown column 'Joe' in 'where clause'... where
"Joe"
> represents the value in $f_name.
>
> I've restructured the statement several times but with no success. Is this
a
> bug in MySQL ?
>
>