modifying url shortner script

I've be开发者_如何学Pythonen 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>

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
  }
}

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()."' 
    ) 
"); 

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.

Simply adding UNIQUE to the database column url_link should do.

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.