Využití unikátních klíčů v databázi

Školení, která pořádám

Článek vyšel v rámci PHP okénka na serveru Root.cz.

Pokud má webová aplikace možnost registrace uživatelů, obvykle jim dovoluje zvolit uživatelské jméno, pod kterým se k aplikaci budou přihlašovat. Obvykle také chceme, aby toto uživatelské jméno bylo jednoznačné (obzvláště pokud jim na jeho základě vytvoříme e-mailovou adresu nebo URL). Dá se to jako obvykle řešit několika způsoby.

<?php
// špatný kód
if (mysql_result(mysql_query("SELECT COUNT(*) FROM uzivatele WHERE login = '" . mysql_real_escape_string($_GET["login"]) . "'"), 0)) {
    echo "Uživatel již existuje.\n";
} else {
    mysql_query("INSERT INTO uzivatele (login) VALUES ('" . mysql_real_escape_string($_GET["login"]) . "')");
}

// opravený kód
mysql_query("LOCK TABLES uzivatele WRITE");
if (mysql_result(mysql_query("SELECT COUNT(*) FROM uzivatele WHERE login = '" . mysql_real_escape_string($_GET["login"]) . "'"), 0)) {
    echo "Uživatel již existuje.\n";
} else {
    mysql_query("INSERT INTO uzivatele (login) VALUES ('" . mysql_real_escape_string($_GET["login"]) . "')");
}
mysql_query("UNLOCK TABLES");

// elegantní kód, předpokládá existenci unikátního klíče nad sloupcem login
mysql_query("INSERT INTO uzivatele (login) VALUES ('" . mysql_real_escape_string($_GET["login"]) . "')");
if (mysql_errno() == 1062) {
    echo "Uživatel již existuje.\n";
}
?>

Proč je první kód špatně? Důvod je ten, že k webové aplikaci může přistupovat více uživatelů zároveň a tím pádem mezi provedením SELECT a INSERT může tyto dvě operace provést někdo jiný a uživatel se tak vloží dvakrát. Je to sice spíše teoretická možnost, ale je lepší si ušetřit bezesné noci hledáním takovýchto problémů. Ve většině databází se tento problém řeší transakcemi, MySQL je však umožňuje pouze za určitých okolností a jako náhradu nabízí zamykání tabulek.

To používá druhý příklad, který je už po formální stránce správně, je ale poněkud krkolomný a skrývá riziko neodemčení tabulky (pokud by uživatel přerušil provádění skriptu před odemčením tabulky a pokud se používá persistentní připojení k databázi, tak už si z tabulky uzivatele nikdo nic nepřečte). Toto riziko se dá řešit doplněním kódu register_shutdown_function(create_function('', 'mysql_query("UNLOCK TABLES");')); před zamknutím tabulky, to se už ale krkolomnost dostává na kritickou hranici.

Třetí způsob je naproti tomu elegantní. Předpokládá pouze existenci unikátního indexu nad sloupcem login, ten by ale měl existovat tak jako tak, protože výrazně urychluje dotazy do tabulky omezené tímto sloupcem. MySQL chyba č. 1062 nastane v případě duplicity unikátního klíče.

Přijďte si o tomto tématu popovídat na školení Návrh a používání MySQL databáze.

Jakub Vrána, Řešení problému, 25.3.2005, diskuse: 26 (nové: 0)

Diskuse

mifo:

Pouzitie $_GET[login] je dost security hazard podla mna. Najprv by sme mali otestovat ci v tej premmennej je naozaj to co chceme aby tam bolo.
A imho radsej by som pouzil $_POST alebo $_REQUEST. ;)

ikona Jakub Vrána OpenID:

Kód předpokládá zapnuté magic_quotes_gpc, security hazard to tedy není. Ukázky kódu jsou jen s nejnutnějším kontextem, obdobně tam chybí vypsání HTML hlaviček :-).

Máš pravdu, že $_POST by bylo lepší než $_GET.

ikona Jakub Vrána OpenID:

Kód jsem předělal na magic_quotes_gpc = Off a zmínil jsem to v patičce.

pavel:

pokud by se login dale pouzival i k jinym ukonum (napr. e-mail, url...) asi by i bylo vhodne pouzivat pouze jednu verzi co se tyka moznosti hacku a carek.

Tedy pokud by jiz existoval uzivatel "liška", nemohl by se vytvorit uzivatel "líska".

Dave Lister:

prvni zpusob je vhodny treba kdyz mam registraci rozlozenou na dve casti a potrebuju zkontrolovat zda login uz neni v jiny tabulce nez kam vkladam "nepotvrzeny uzivatele"

black_jack:

no, ja ukladam do table md5($login) a md5(pwd), souhlas - neni to tolik bezpecny - magic_quotes_gpc myslím nevyřeší vše

bohdy:

Jen takovy dotaz k opravenemu kodu - jak muze fungovat, kdyz je tabulka zamkla? :)

ikona Jakub Vrána OpenID:

Buď jsi špatně formuloval dotaz nebo sis nenastudoval, k čemu je v MySQL zamykání tabulek.

bohdy:

Aaa, omlouvam se a sypu si popel na hlavu :)

ikona v6ak:

Ještě by to chtělo rozeznat sloupec, ve kterém k porušení došlo. Pokud je unikátní třeba jméno i mail, tak se to hodí. Šlo by to z *errstr, ale bude to asi závislé na použité DB (používám PDO).

štíhloprd:

Problém k tématu:

Dejme tomu, že bych měl tabulku předplatitelů časopisů nebo kulturních akcí - to je jedno; co řádek to člověk; a měl bych poté, co každý z nich odešle formulář s přihláškou, mu přidělit variabilní symbol pro bankovní platbu (dejme tomu osmimístné číslo), které musí být unikátní pro všechny platby.

Třeba v MS SQL údajně existuje dokonce typ náhodné číslo (jmenuje se to asi jinak), ale jak u MySQL? S přihlédnutím k tomuto článku mě napadá tohle:

<?php
define
('POCET_POKUSU',100); //napr.
...
$sql1='INSERT INTO predplatitele(seznam poli..) VALUES bla="bla", bla="atd"..., variabilni_symbol="';
$sql2='");';
for(
$i=0;$i<POCET_POKUSU;$i++){
$vs=rand(1000,9999).rand(1000,9999);
mysql_unbuffered_query($sql1.$vs.$sql2);
if(
mysql_errno()!=1062)break;
}
// zapomněl jsem zmínit - nad sloupcem variabilni_symbol by byl UNIQUE KEY
?>

V tom obzvlášť a nejnejnejnepříznivějším případě se můžu 100x strefit do existujících variabilních symbolů a vložení záznamu selže. Co byste mi poradili?

Mám to vůbec řešit přes generování náhodného čísla? Odvodit to číslo od TIMESTAMP? Nebo nějak jinak? Díky

ikona Jakub Vrána OpenID:

Osobně bych tam dal číslo ze sekvence. Pokud by to mělo být náhodné číslo, bylo by potřeba vygenerovat náhodné číslo z rozsahu MIN..MAX-x, kde x je počet už vygenerovaných čísel a vzít tolikáté volné číslo – to je spolehlivý algoritmus nezávislý na smůle a bez vytváření shluků.

štíhloprd:

Promiň, ale nerozumím. Tomu, jak by taková sekvence měla vypadat a dále části "a vzít tolikáté volné číslo" (co znamená 'volné číslo').
Pro můj příklad je:
define(MIN,10000000);
define(MAX,99999999);
/*tabulka je zatím prázdná, takže*/ $x=0;
$cislo=rand(MIN,MAX-$x); // např. 12345678
.. co dál? Mohl bys naznačit první a druhou iteraci?

ikona v6ak:

Pokud to chápu dobře, tak si vytvoříš tabulku se všemi náhodnými čísly, pak zamícháš a pak bereš jedno po druhým.

ikona Jakub Vrána OpenID:

Myslel jsem to takhle:
<?php
$cislo
= rand(MIN, MAX - count($vygenerovane));
for (
$i=MIN; $i <= $cislo; $i++) {
    if ($vygenerovane[$i]) {
        $cislo++;
    }
}
?>
Proměnná $vygenerované obsahuje seznam vygenerovaných čísel (v klíči).

štíhloprd:

Jenže, co když mám těch čísel tolik, že se mi nevejdou do paměti (do pole $vygenerovane)?
Tvoje řešení (pokud to chápu dobře) navíc vyžaduje, aby pole $vygenerovane bylo již naplněno použitými variabilními symboly. :-(

ikona Jakub Vrána OpenID:

Ještě by se dal zjistit počet předchozích pomocí dotazu SELECT COUNT(*) FROM predplatitele WHERE variabilni_symbol <= $cislo. O tento počet by se $cislo zvedlo a tak pořád dokola, dokud by dotaz nevrátil 0. Problém je, že by se dotaz musel volat v cyklu, což je nepříjemné.

Další možnost je položit dotaz

SELECT cisla.cislo
FROM cisla
LEFT JOIN predplatitele ON cisla.cislo = predplatitele.variabilni_symbol
WHERE predplatitele.variabilni_symbol IS NULL
ORDER BY cisla.cislo
LIMIT 1 OFFSET $cislo

To by ale zase vyžadovalo mít tabulku se všemi možnými čísly, protože neznám způsob, jak si ji přinejmenším v MySQL vytvořit virtuálně.

ikona Jont:

Rád bych tento způsob použil, ale jak udělat, aby se po uživateli uložilo i heslo k tomu uživateli? Dát do insert into where nejde, to jsem zkoušel (mám to jako součást registračního formuláře, kde set testují i email atd.
Poradíte někdo? Asi je to úplná blbost, ale mě to nejde. Dík

ikona Jont:

Jsem já to ale vůl, vždyť stačí použít místo insert UPDATE. Už mě to jede, omlouvám se za zbytečný dotaz.

3wl4k:

http://bugs.mysql.com/bug.php?id=28842

<?php

public function connect( ...)
{
    // ...

    if( $version < 5001000){
        $this->duplicateErrNo = 1062;
    } else {
        $this->duplicateErrNo = 1582;
    }

}

?>

alebo mi nieco uslo? :))

ikona Jakub Vrána OpenID:

Platilo to jen ve verzích 5.1.15 až 5.1.19, pak se vrátilo původní číslo chyby. Viz http://dev.mysql.com/doc/refman/5.1/en/news-5-1-20.html.

3wl4k:

Cize:

<?php

if( ($version < 5001015) || ($version > 5001019)){
    $this->duplicateErrNo = 1062;
} else {
    $this->duplicateErrNo = 1582;
}

?>

je uz spravne?

Belveder:

Jen takový malý dotaz. Co se stane když se pokusí zapisovat jiný script do tabulky která je zamčená?

ikona Jakub Vrána OpenID:

Čeká, dokud se zámek neuvolní.

Vladimír Vojík:

Ahoj Jakube, jak by se prosím dala řešit situace, kdy je unikátních sloupců v MySQL více (např. jméno a e-mail) a chtěl bych zjistit, že právě u daného sloupce došlo k chybě 1062? Moc děkuji.

ikona Jakub Vrána OpenID:

Leda parsováním chybové hlášky, která je ve tvaru „Message: Duplicate entry '%s' for key '%s'“. Z toho se dá zjistit název unikátního klíče, z kterého se pak dá zjistit, které sloupce pokrývá.

Diskuse je zrušena z důvodu spamu.

avatar © 2005-2024 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.