Storing and Comparing URLs

D_tunisia

Hi all,

I have some code to compare the URL entered in a form with ones stored in a DB to ensure that a duplicate is not entered but the query never finds a match.

$amount = mysql_query("SELECT * FROM bookmarks WHERE url = $url");
$counter = mysql_num_rows($amount);


if ($counter > 0) {
	$system_message[] = "DUPLICATE LINK"; // ADD A SYSTEM MESSAGE
	$val++; }

If I manually enter a URL into the query to compare it does work. For example:

$amount = mysql_query("SELECT * FROM bookmarks WHERE url = 'http://www.google.com'");
$counter = mysql_num_rows($amount);


if ($counter > 0) {
	$system_message[] = "DUPLICATE LINK"; // ADD A SYSTEM MESSAGE
	$val++; }

There is clearly some difference between how PHP sees the URL from the DB and the URL which has been entered in the form.

Can anyone help me out?!

NogDog

You need single quotes around the $url variable in the query string. I suspect the query is throwing an error as currently configured, which you would have trapped if checked the result of the mysql_query() command.

Also, are you escaping the user-supplied $url value with mysql_real_escape_string() before using it in your query? (I hope so, or you are open to sql injection attacks.)

coldwerturkey

Sorry for hijacking this thread. but twice now I've seen you mention mysql_real_escape_string() today. I php.net and googled it but didn't get a simplified explanation of what it is, and why its so important,
would you mind explaining it in layman's terms please?

laserlight

coldwerturkey wrote:
Sorry for hijacking this thread. but twice now I've seen you mention mysql_real_escape_string() today. I php.net and googled it but didn't get a simplified explanation of what it is, and why its so important,
would you mind explaining it in layman's terms please?

Suppose you have a table named Students. Suppose also you are inserting some student records. Perhaps you come up with this piece of code:

mysql_query("INSERT INTO Students ('name') VALUES ('$student_name')");

Now, suppose there is a student named "Robert'); DROP TABLE Students;--". (The -- marks the beginning of a comment, much like // and # in PHP.) Now, your code would effectively become:

mysql_query("INSERT INTO Students ('name') VALUES ('Robert'); DROP TABLE Students;--')");

Assuming that both SQL statements were executed (it might not, e.g., lack of permission to drop the Students table, unique constraint violation on the name column, or even the MySQL extension not allowing mysql_query() to be used to execute more than one SQL statement at a time), the entire Students table would be dropped. This is clearly a Bad Thing. For a somewhat more graphical explanation of what I have outlined, refer to the Exploits of a Mom.

With the use of a proper escaping mechanism like [man]mysql_real_escape_string/man, the single quote in "Robert'); DROP TABLE Students;--" would be escaped, so this SQL injection could not occur.

Of course, I suggest to both D_tunisia and you to switch to the PDO extension or MySQLi extension instead and use prepared statements as an escaping mechanism where possible.