SQL Query??

Tanax · 11-21-2007, 08:57 AM

Hi, I'm reading a tutorial about creating a simple discussion board(on pixel2life.com), and I was kinda confused about this sql statement in his code:

php Code:

// SQL statement

        $sql = "SELECT `id` FROM `".SUFFIX."user` WHERE ((md5(`username`) = '".md5($username)."') && (`password` = '".md5($password)."')) LIMIT 1";

The suffix thingy is defined, if you want to have more than 1 forum on the same db.

But the thing that's confusing me is the

Code:

(md5(`username`) = '".md5($username)."')

The $username will be the $_POST['username'] value, that the user logs in with.

If he md5 that value, it will be something like 13057235ngw8tg34g.
Then he md5 the value of the username row in the db. And if it matches the value of the $_POST input, the value of the username row have to be... the actual username.

So what's the point in md5'ing it? Because he's still storing the username in the db without any hash...? :confused: :confused: :confused: :confused: :confused: :eek:

Salathe · 11-21-2007, 09:05 AM

There is no apparent point, other than wasting CPU cycles on needless MD5ing.

Tanax · 11-21-2007, 09:21 AM

Okey!
Thanks.

One more question, would this be a better query?

php Code:

$sql = sprintf("    SELECT 
                                    `".$this->db->col['user_id']."` 
                                FROM 
                                    `".$this->db->table['users']."` 
                                WHERE 
                                    ((`".$this->db->col['user_name']."` = '%s') && (`".$this->db->col['user_pass']."` = '%s')) 
                                LIMIT 1", 
                                    
                                $user_name,
                                md5($user_pass));

bluesaga · 11-21-2007, 09:53 AM

technically yes, as long as you are using mysql_escape_string on $user_name and $user_pass

Tanax · 11-21-2007, 10:22 AM

Yes ofcourse, but that's done outside of the query ;)
Thanks

bluesaga · 11-21-2007, 11:52 AM

Well personally, i do it in the sprintf when you are defining the variables. But its personal preference really, i guess :)

Salathe · 11-21-2007, 01:12 PM

I don't know why you're using parentheses and the && comparison operator. Why not just go the normal route?

sql Code:

--- Compare
SELECT a FROM b WHERE ((c = 'c') && (d = 'd')) LIMIT 1
--- With
SELECT a FROM b WHERE c = 'c' AND d = 'd' LIMIT 1

Also, since you're using sprintf, why are you concatenating the table/column names into the formatting string?! Make proper use of the function, or don't use it at all, rather than mixing and matching.

(Use back ticks to wrap table and column names if you want/need to)

php Code:

$szSql = sprintf("SELECT %s FROM %s WHERE %s = '%s' AND %s = '%s' LIMIT 1",
                 $this->db->col['user_id'],
                 $this->db->table['users'],
                 $this->db->col['username']
                 $user_name,
                 $this->db->col['user_pass'],
                 md5($user_pass));

Tanax · 11-21-2007, 01:36 PM

Quote:

Originally Posted by Salathe

I don't know why you're using parentheses and the && comparison operator. Why not just go the normal route?

sql Code:

--- Compare
SELECT a FROM b WHERE ((c = 'c') && (d = 'd')) LIMIT 1
--- With
SELECT a FROM b WHERE c = 'c' AND d = 'd' LIMIT 1

Also, since you're using sprintf, why are you concatenating the table/column names into the formatting string?! Make proper use of the function, or don't use it at all, rather than mixing and matching.

(Use back ticks to wrap table and column names if you want/need to)

php Code:

$szSql = sprintf("SELECT %s FROM %s WHERE %s = '%s' AND %s = '%s' LIMIT 1",
                 $this->db->col['user_id'],
                 $this->db->table['users'],
                 $this->db->col['username']
                 $user_name,
                 $this->db->col['user_pass'],
                 md5($user_pass));

Well, as I said, the SQL QUERY was from a tutorial that I was reading, I just copy pasted it, because I was confused about the md5 thing.

Anyways, I don't know why i do that :| I'll edit it ;)

THanks :)

Tanax · 11-21-2007, 05:34 PM

Okey, I got another problem regarding SQL queries.
I couldn't be bothered creating a new topic just for that when I already have this one..

Quote:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\DB Class 2\includes\classes\user.php on line 126

user.php

php Code:

public function user_is_logged_in() {
            
            $sql = sprintf("    SELECT
                                    `%s`
                                FROM
                                    `%s`
                                WHERE
                                    `%s` = '%s'
                                LIMIT 1",
                                
                                $this->db->col['user_id'],
                                $this->db->table['users'],
                                $this->db->col['user_session'],
                                session_id());
                                
            $query = $this->db->query($sql);
            
            if(!mysql_num_rows($query)) {
                
                return false;
                
            }
            
            return true;
            
        }

Salathe · 11-21-2007, 05:36 PM

Echo out the finished $sql string and see if it is malformed.

Tanax · 11-21-2007, 05:37 PM

Okey! I'll try

Tanax · 11-21-2007, 05:45 PM

Returned this:

Code:

SELECT `user_id` FROM `users` WHERE `user_session` = '6dcf1236d6dddf1aed86625d22af0e78' LIMIT 1

The problem(I think) was that it didn't find anything. Because when I did a @mysql_num_rows the code in index.php was working...

php Code:

if(!$tanaxia['user']->user_is_logged_in()) {
echo 'Not logged in!';
}
else {
echo 'Logged in';
}

And it echoed not logged in.. so I guess it worked? :P