[MySQL] volgorde van een Update aangeven

[Zoetjuh]

Hoi People,

Ik probeer een aantal records in een bepaalde volgorde te plaatsen. Nu heb ik een tabel met een hoop records, waarvan alle records met het zeflde groepnummer bij elkaar horen.. Binnen zo'n groep wordt een sortering toegepast om de items in een correcte volgorde te krijgen. Dit doe ik door de "OS2_VRAAGNO"-waarde. Zo is het dus dat het record waarbij deze waarde '0' is, bovenaan komt te staan. De gene met de waarde '1' hieronder.

Voorbeeld:
OS2_VRAAGNO (0); OS2_ID(54)
OS2_VRAAGNO (1); OS2_ID(55)
OS2_VRAAGNO (2); OS2_ID(56)

Deze records worden via PHP in een select-box gegooid, gesorteerd op de OS2_VRAAGNO-waarde. Mensen kunnen met knopjes hun eigen sortering maken van de records..

En nu komt het punt.. wanneer mensen OS2_ID(56) nu voor (55) hebben geplaatst zou ik dus dit moeten krijgen:

OS2_VRAAGNO (0); OS2_ID(54)
OS2_VRAAGNO (1); OS2_ID(56)
OS2_VRAAGNO (2); OS2_ID(55)

Nu heb ik de volgende query om de records te nummeren:

SET @n=0; UPDATE OFFERTE_STEP2_Q SET OS2_VRAAGNO = (SELECT @n:=@n+1) WHERE OS2_ID IN (54, 56, 55)

Helaas bemerk ik dat MySQL zich niet aan mijn IN(...) volgorde houdt, maar ze alsnog fijn sorteerd op die OS2_IDs (waardoor 55 toch weer voor 56 belandt)

Heeft iemand misschien een idee hoe ik alsnog mijn sortering voor elkaar kan krijgen? En dan uiteraard het liefst met 1 UPDATE query...

Thanks

[ Voor 7% gewijzigd door Zoetjuh op 29-11-2004 17:49 . Reden: verbetering ]

[NMe]

Volgens mij kan het niet wat je wil, tenzij je allerlei losse queries gaat maken.

[kvdveer]

-NMe- schreef op maandag 29 november 2004 @ 17:55:
Volgens mij kan het niet wat je wil, tenzij je allerlei losse queries gaat maken.

MySql ondersteunt een order by clause in de update. Die is overigens alleen bedoeld om te gebruiken in combinatie met LIMIT. Als je die dus gaat gebruiken om vage dingen te doen met variabelen gebruik je undocumented (en wellicht unintended) functionaliteit. Grote kans dat je scriptje dan niet meer werkt na een update van MYSQL.

Je doet er verstandig aan om het gewoon in losse queries te doen.

[ACM]

Waarom doe je het niet gewoon zo:

SQL:

UPDATE OFFERTE_STEP2_Q SET OS2_VRAAGNO = 
case OS2_ID when 54 then 0 when 56 then 1 when 55 then 2 else OS2_VRAAGNO end
WHERE OS2_ID IN (54, 56, 55)

[Zoetjuh]

ACM schreef op maandag 29 november 2004 @ 18:15:
Waarom doe je het niet gewoon zo:

SQL:

UPDATE OFFERTE_STEP2_Q SET OS2_VRAAGNO = 
case OS2_ID when 54 then 0 when 56 then 1 when 55 then 2 else OS2_VRAAGNO end
WHERE OS2_ID IN (54, 56, 55)

Daar vraag je me wat
Das goed te coden met PHP ja.. hmmm ga ik direct mee testen. thanks!

Edit:
Dat was het inderdaad. Mijn dank is groot.

Ter compleetheid; het result:

code:

    // Execute query:
    $myIDs = explode(",", $getVars["IDs"]);

    $myQuery = "UPDATE OFFERTE_STEP2_Q SET OS2_VRAAGNO = "
          ." CASE OS2_ID";
            
    foreach ($myIDs as $key => $value)
        $myQuery .= " WHEN " .$value ." THEN " .$key;
 
    $myQuery .=" ELSE OS2_VRAAGNO END "
          ."WHERE OS2_ID IN (" .$getVars["IDs"] .")";

    mysql_query($myQuery);

[ Voor 45% gewijzigd door Zoetjuh op 29-11-2004 19:05 ]

[ACM]

Zoetjuh schreef op maandag 29 november 2004 @ 18:50:

code:

    // Execute query:
    $myIDs = explode(",", $getVars["IDs"]);

    $myQuery = "UPDATE OFFERTE_STEP2_Q SET OS2_VRAAGNO = "
          ." CASE OS2_ID";
            
    foreach ($myIDs as $key => $value)
        $myQuery .= " WHEN " .$value ." THEN " .$key; // <-- hier
 
    $myQuery .=" ELSE OS2_VRAAGNO END "
          ."WHERE OS2_ID IN (" .$getVars["IDs"] .")"; // <-- en hier

    mysql_query($myQuery);

Let er op dat je twee punten voor potentiele SQL-injectie hebt.

[Zoetjuh]

Hoe bedoel je dat precies?
Opzich bouw ik hier toch alleen een query op, waarna ik deze pas ga uitvoeren toch? Of mis ik iets...

[ACM]

Als je zeker weet dat in die $getVars['IDs'] alleen maar gecontroleerde inhoud zit, dan heb je nergens last van. Maar als je direct de waarden van een gebruiker er in invoert, dan wordt het vervelender.
In dit geval is het niet echt goed mogelijk om de query uit te breiden tot iets echt gevaarlijks, maar er altijd rekening mee houden is beter dan af en toe vergeten dat er wat kan gebeuren.

Die deze faq-pagina voor meer info over SQL-injectie
P&W FAQ - Hoe beveilig ik een website?

[ Voor 13% gewijzigd door ACM op 29-11-2004 21:17 ]

[Zoetjuh]

Zal je voor de volledigheid een stukje van de code laten zien vanuit waar ik bovenstaande aanvraag. Ik neem aan dat het op deze manier toch wel wel veilig is?
----

code:

// Get the select list:
myItems = document.getElementById("myItems");
myStr   = "";

// Set the items in the string:
// --
for (i=0; i < myItems.options.length; i++)
    if (i>0) myStr += ", " + myItems.options[i].value;
    else     myStr  = "" + myItems.options[i].value;

// Add to Queue:
// --
if (myStr != "")
    addToQueue("orderItems", "IDs=" + myStr);

-------
Die addToQueue is een JavaScript "queue" die ik heb gemaakt, die de toegevoegde uitvoercommando's in een apart iframe uitvoerd en zo stap voor stap door de queue heen loopt (daar worden dus de MySQL-commando's uitgevoerd):
-------

code:

function CommandType(command, variable)
{
this.command     = command;
this.variable    = variable;
}

function addToQueue(myCommand, variable)
{
var myObj   = new CommandType();
myObj.command   = myCommand;
myObj.variable  = variable;

myActions.push(myObj);
if (!inExec)  execQueue();
}

function execQueue()
{
// Re-enable visual inputs and check values:
fullLockDown(false);
checkPs();

// Execure the Queue:
if (myActions.length > 0)
    {
    inExec = true;
    myCommand = myActions.shift();

    if (myCommand.command == "updateType")   onUpdateType(myCommand.variable);
    if (myCommand.command == "updateName")   onUpdateName(myCommand.variable);
    if (myCommand.command == "addItem")      onAddItem(myCommand.variable);
    if (myCommand.command == "deleteItem")   onDeleteItem(myCommand.variable);
    if (myCommand.command == "orderItems")   onOrderItem(myCommand.variable);


    myCommand = null;
    }
}

// *********************************
// ******* Execute function ********
// *********************************

function onOrderItem(variable)
{
// Disable visual inputs:
fullLockDown(true);

// Execute:
variable = variable.replace(/\+/g,"%2B");
document.getElementById("ifrExecPHP").src = "/cgi-bin/zzp-mmg-proj-qs-edt.php?act=order&" + variable;
}

---
Nadat het het PHP/MySQL-verhaal in het iFrame is uitgevoerd, zet deze de 'inExec'-waarde weer op false en roept de execQueue weer aan om zo het volgende commando uit de queue uit te kunnen voeren (wanneer die er is).

PS. Tips zijn natuurlijk toch wel altijd van hartte welkom hoor

[ Voor 33% gewijzigd door Zoetjuh op 29-11-2004 22:23 . Reden: aanvulling ]

[blizt]

Ik zou toch maar 'ns kijken naar dingen als addslashes() en htmlspecialchars() als ik jou was
Sowieso nooit op JS vertrouwen, aangezien dat clientside is & makkelijk uit te schakelen/omzeilen.

[ACM]

In principe mag je de client-side niet vertrouwen, dus ook geen zelfgeschreven forms, javascript etc kan aangepast worden en daarmee potentieel gevaarlijke code voor je server-side creeeren en door je gebrek aan controle ook uit laten voeren.
Zoals gezegd valt het potentiele misbruik in dit geval waarschijnlijk wel mee, maar het is erg verstandig om van alle client-side informatie eerst te controleren of het wel voldoet aan de verwachtingen die je er van hebt.

Wil je integers in lijstje? Controleer dan of het een lijstje integers is. Wil je een enkel getal? Controleer dan of je ook daadwerkelijk een getal gekregen hebt.
En dus ook als jij denkt dat de enige manier waarop mensen met jouw server-side code omgaan, via zelfgeschreven client-side code is. Je hebt geen controle over die client-side, dus die moet je ook nooit vertrouwen. Doe je dat wel, dan stel je jezelf bloot voor gevaarlijke code.
En in sommige applicaties kan dat zeer kwalijke gevolgen hebben.

Stel je bijvoorbeeld voor dat een internet-bankier website het bankrekeningnummer van een betaler via een hidden input zou meegeven aan een betaling en niet zou controleren of dat nummer wel van die betaler is, maar domweg zou overnemen in de betaling. In dat geval kan je zo'n hidden input aanpassen naar het rekeningnummer van iemand anders en zo (voor jou) gratis een hoop mensen betalen...
Met SQL-injectie kunnen vergelijkbare dingen gedaan worden, bijvoorbeeld bij het aanpassen van wachtwoorden gebruik maken van ongecontroleerde opname van strings in queries. Om een voorbeeld te noemen: stel dat het mogelijk is om naast je eigen userid ook de userid van een administrator toe te voegen (met SQL-injectie kan dat af en toe wel degelijk!), waarna jij en die administrator hetzelfde wachtwoord hebben als je je wachtwoord wijzigt...

Meer leeswerk en voorbeelden:
[rml][ SQL / MySQL] SQL Injection mogelijkheden en bescherming[/rml]

[ Voor 3% gewijzigd door ACM op 29-11-2004 22:40 ]

[ACM]

blizt schreef op maandag 29 november 2004 @ 22:32:
Ik zou toch maar 'ns kijken naar dingen als addslashes() en htmlspecialchars() als ik jou was
Sowieso nooit op JS vertrouwen, aangezien dat clientside is & makkelijk uit te schakelen/omzeilen.

Htmlspecialchars is niet zo zinvol in de database-wereld.

[blizt]

Point ACM, maar toch ... Je weet nooit wat voor rare HTML-dingen mensen er in willen proppen die jij vervolgens lekker weergeeft?

[Zoetjuh]

Ik begrijp je punt inderdaad.. en ik moet toegeven dat ik misschien toch wel wat meer op input zou kunnen controlen om het mislukken van queries te voorkomen.

Nu is het wel zo dat je alleen items kan aanpassen die horen bij de persoon waarin je op de sessie bent ingelogd. Dit heb ik inderdaad niet laten zien in het stukje query, maar dat heb ik wel zo veel mogelijk proberen te verwerken.. (door te controleren op session IDs en door alle queries, voor zover mogelijk, te voorzien van een where-clause waarin gecontroleerd wordt dat de items wel onderdeel zijn van de ingelogde persoon..)

Misschien is extra controle op het type input inderdaad geen slecht idee.. ook al, zover ik kan zien, zal foutieve invoer alleen voor het mislukken van queries zorgen.. Maargoed, om dat netter af te vangen is natuurlijk alleen maar een goed iets.. (nooit alleen uitgaan van de juiste handelingen)

True, het is een eenmansproject (van 2~3 maanden, 30 uur per week) en er zal nog wel verder op lekken gecontroleerd moeten worden... Ik zal ook die addslashes() en htmlspecialchars() eens extra in de gaten houden.. Maar zou ik dat nou echt voor *iedere* variable moeten gebruiken.. lijkt me toch alleen nodig voor text-inputs.. (verwacht antwoord: en ook voor alle info die je via posts en gets binnen krijgt)

PS.
Wanneer men JS uitschakeld valt er weinig aan de site te beheren.. Vermoed dat dit wel nodig is..

PS2.
Ook HTML lijkt me net zo aanpasbaar.. dus lijkt me toch dat sessie-controle (en dus input controle) hetgeen is waarmee ik middels PHP kan controleren.. bedoel.. JS en HTML/DOM heb *je* toch gewoon nodig (en ja, tis allemaal netjes HTML4.01 gecertificeerd)

PS3.
Ik heb de docu's gelezen en begrijp je nog beter ACM.. Maar als je heeeeel misschien stiekem de behoefte hebt om vluchtig te kijken, you're more than welcome (if so, let me know)

[ Voor 53% gewijzigd door Zoetjuh op 30-11-2004 01:44 . Reden: aanvulling (again) ]

[bartvb]

Eeh, ja, je zal _alles_ dat je binnen krijgt moeten controleren. Het is echt een koekje om jouw javascript applicatie (gedeeltelijk) te vervangen door een zelfgebouwd ander scriptje dat dezelfde info verstuurd via GET/POST/COOKIE. Zo'n PHP sessie zegt al helemaal niets, het is alleen een nummer dat via een Cookie of via GET meegestuurd wordt.

Neem van mij aan dat je echt alles zult moeten controleren voor je het ergens in je database stopt. Strings sowieso door addslashes halen, integers controleren of ze ook echt (geldige) integers zijn, etc, etc.

En nee, als je input niet controleert dan kan dat niet alleen leiden tot ongeldige SQL queries. Dom voorbeeldje:

code:

mysql_query("SELECT * FROM movies WHERE movie_id = " . $_GET['movie_id'])

Als ik dan zorg dat $_GET['movie_id'] iets bevat als:

code:

1; INSERT INTO users (username, password, level) VALUES ('toor', MD5('haxorz'), 'admin')

Dan kan ik daarna inloggen als admin. Niet echt wenselijk gok ik

Hm, zie nu dat dat ook gewoon uitgelegd wordt in P&W FAQ - Hoe beveilig ik een website?