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.
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. ;)
Jakub Vrána 
:
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.
Jakub Vrána : Jakub Vrána :
Kód jsem předělal na magic_quotes_gpc = Off a zmínil jsem to v patičce.
Jakub Vrána : 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šebohdy:
Jen takovy dotaz k opravenemu kodu - jak muze fungovat, kdyz je tabulka zamkla? :)
Jakub Vrána :
Buď jsi špatně formuloval dotaz nebo sis nenastudoval, k čemu je v MySQL zamykání tabulek.
Jakub Vrána : bohdy:
Aaa, omlouvam se a sypu si popel na hlavu :)
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).
v6ak: š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
Jakub Vrána :
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ů.
Jakub Vrána : š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?
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.
v6ak: Jakub Vrána :
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).
Jakub Vrána : š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. :-(
Jakub Vrána :
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ě.
Jakub Vrána : 
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
Jont: 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.
Jont: 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? :))
Jakub Vrána :
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.
Jakub Vrána : 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á? Jakub Vrána :
Čeká, dokud se zámek neuvolní.
Jakub Vrána : 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. Jakub Vrána :
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á.
Jakub Vrána : Diskuse je zrušena z důvodu spamu.
© 2005-2024 