[SOLVED] concatenating strings in mysql - what's the syntax?

[longtone]

I know this is really basic, but I can't find the syntax for this:

 

Having set a cookie with the value:

 

md5($username . $key)

 

where $key is an integer

 

I want check the cookie against the column username

 

Can I use something along the lines of:

 

SELECT*FROM table WHERE md5(username' . $key') = '$cookie' 

 

and what would be the syntax?

[cooldude832]

CONCAT()

 

but I don't see the usefulness of what you are doing

SELECT * FROM `table` WHERE md5(CONCAT(username,$key)) = '$cookie'

 

Because you won't get any "rows" that match the criteria

[fenway]

but I don't see the usefulness of what you are doing

It's a salt.

 

FYI, it's better (more secure) to do this in php, and then pass the salted-and-MD5'd string to the db.

[cooldude832]

so it will select all or nothing from the table?

 

That was what I meant I don't see it specifying a single row

[fenway]

I'm not sure what you mean... but $cookie will not match that string directly, no.

[longtone]

CONCAT()

 

but I don't see the usefulness of what you are doing

SELECT * FROM `table` WHERE md5(CONCAT(username,$key)) = '$cookie'

 

Because you won't get any "rows" that match the criteria

 

Thanks, that does it

 

(except I needed to put the $key in single quotes)

 

The usefulness is to set an encrypted cookie, so when the user returns I can recognize them.

 

I'll follow that line with something like:

 

 

	if (mysql_num_rows($result) !=0) {

	   $row = mysql_fetch_array($result);

	   $username = $row['username'];


	} 

 

[longtone]

but I don't see the usefulness of what you are doing

It's a salt.

 

FYI, it's better (more secure) to do this in php, and then pass the salted-and-MD5'd string to the db.

 

Why would that be more secure?

 

The list of usernames isn't sensitive information, as they can all be seen from the front end anyway, although it might be worth encrypting the information linked to those usernames.

 

But I want to make it difficult for someone to fake a cookie by encrypting it with a salt.

 

Or do you mean generate a random value for the cookie, then store the salted-and-MD5'd string in another column in the table to check it against?

 

I suppose that would be more secure, as it would be more, well, random.

[cooldude832]

fennway if the where clause of that query matches i.e md5() = $cookie then isn't the query really similar to

select * from `table` Where 1=1 

Meaning it will select all rows

 

and if the md5() != $cookie it will be exactly like

select * from `table` Where 1 = 2

which returns 0 rows?

[longtone]

fennway if the where clause of that query matches i.e md5() = $cookie then isn't the query really similar to

select * from `table` Where 1=1 

Meaning it will select all rows

 

and if the md5() != $cookie it will be exactly like

select * from `table` Where 1 = 2

which returns 0 rows?

 

It only returns the row where the value in the column 'username', when salted and md5'd, gives the result equal to $cookie:

 

		$key = 76589087;

	$user = 'John';

	$cookie = md5($user . $key);

//$cookie is set, and then later retrieved and checked:


	$result = mysql_query("SELECT*FROM table WHERE md5(CONCAT(username,'$key')) = '$cookie' ");


	if(mysql_num_rows($result) !=0){

	$row = mysql_fetch_array($result);

	$username = $row['username'];

		echo 'ok: ' . $username;

	} else {

		echo 'no';

	}

 

[cooldude832]

I didn't realize username was the mysql variable in the CONCAT now it makes sense otherwise I don't see it being row specific

[corbin]

but I don't see the usefulness of what you are doing

It's a salt.

 

FYI, it's better (more secure) to do this in php, and then pass the salted-and-MD5'd string to the db.

 

Why would that be more secure?

 

The list of usernames isn't sensitive information, as they can all be seen from the front end anyway, although it might be worth encrypting the information linked to those usernames.

 

But I want to make it difficult for someone to fake a cookie by encrypting it with a salt.

 

Or do you mean generate a random value for the cookie, then store the salted-and-MD5'd string in another column in the table to check it against?

 

I suppose that would be more secure, as it would be more, well, random.

 

 

A MySQL connection could easily be sniffed, and they would see the key being passed to MySQL from PHP.

 

 

"But I want to make it difficult for someone to fake a cookie by encrypting it with a salt."

 

Salting is always good.  But, there is a better way for you to do this.

 

I've seen a lot of times where people have done something like (psuedo-code):

 

if(cookie set for user id) log user in as that id;

 

Which is essentially what you're doing, but you're encrypting the username and adding some salt.

 

A better way to do it (more secure anyway) is to store the username and hashed password.

 

 

 

But, in the big scheme of things, your method will work just as well since it's salted.  (Assuming the salting is strong.)

 

If the salt is 8 characters long, it would take quite sometime to guess the salt for example.

 

 

 

 

Basically this is it:

 

When going from md5(plaintext.salt) -> hash, the more known plaintext.salt is, the easier it is to come across the hash.

 

So, if plaintext is the username, chances are people's user names are publicly known.  Passwords on the other hand aren't, so the chances of someone knowing it are much, much lower.  (And, if they knew the password, they wouldn't be exploiting cookies ;p.)