modifying url shortner script

Question

I've been using Johnboy URL shortner and I noticed it doesn't check and see if the URL that it generates exists in the database. How could I ensure that the URL is unique>

Answer 1

Simply adding UNIQUE to the database column url_link should do.

Answer 2

Create a unique index/constraint for the long or the short url or both. When the script tries to insert another record with the same values the INSERT statement will fail with a specific error code which you can test for and handle appropriately.

define('MYSQL_ER_DUP_ENTRY', 1062);
...
if ( !mysql_query($mysql, $query) ) {
  if ( MYSQL_ER_DUP_ENTRY==mysql_errno($mysql) ) {
    // handle duplicate entry
  }
}

Answer 3

you could use foreign key constraints to ensure unique urls at the database level. Then, in the php, check that the query inserted a row - if so, then you know a unique url has been inserted, if not, then give the user or script a chance to try it again with a new string.

Answer 4

Seems like johnboy's script is full of vulnerabilities...but here ya go! (modified index.php script where it calculates the new short url)

$short = substr(str_shuffle('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'), 0, 5); 
$unique = false;
while(!$unique) {
    $exists = mysql_fetch_assoc(mysql_query("SELECT url_link FROM urls WHERE url_short = '".$short."'")); 
    if($exists['url_link'] != '') {
        // one already exists! create another, try again.
        $short = substr(str_shuffle('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'), 0, 5); 
    } else {
        $unique = true;
    }
}
mysql_query("INSERT INTO urls (url_link, url_short, url_ip, url_date) VALUES 
    ( 
    '".addslashes($_POST['url'])."', 
    '".$short."', 
    '".$_SERVER['REMOTE_ADDR']."', 
    '".time()."' 
    ) 
"); 

Answer 5

The DB table looks like this:

CREATE TABLE IF NOT EXISTS `urls` (
  `url_id` int(11) NOT NULL auto_increment,
  `url_link` varchar(255) default NULL,
  `url_short` varchar(6) default NULL,
  `url_date` int(10) default NULL,
  `url_ip` varchar(255) default NULL,
  `url_hits` int(11) default '0',
  PRIMARY KEY  (`url_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The primary key is a auto-incremented integer that is not used anywhere across the app. You can just get rid of it and use url_short as primary key. You can then do one of three things when inserting new stuff:

1. Use INSERT IGNORE and discard dupes silently.
2. Use INSERT ... ON DUPLICATE KEY UPDATE ... and update dupes.
3. Use regular INSERT and check the error code: if 1062, it's a dupe.

I'd go for #3.

However, considering that it uses addslashes() to inject input parameters into SQL I'd just avoid using this script at all. It looks way obsolete and insecure.