[MySQL] Update statement met value null error...

[dj_vibri]

Allen,

na veel zoek en vloek werk heb ik al een aantal stored procedures kunnen aanmaken.
Maar nu ben ik in de stored procedure voor bv. de tabel 'employee' een actie aan het toevoegen om een record te updaten.

Volgende code komt overeen met de CASE van P_EmployeeAction (welke dus insert, update of delete kan zijn).

code:

WHEN (P_EmployeeAction = 'Update') THEN
    Set @bla:='wiiee';
      Set @Fields := concat(@FEID,'="',P_EID,'",'
                             ,@FEFN,'="',P_EFN,'",'
                             ,@FELN,'="',P_ELN,'",'
                             ,@FEAddress,'="',P_EAddress,'",'
                             ,@FEZip,'="',P_EZip,'",'
                             ,@FECity,'="',P_ECity,'",'
                             ,@FEBirth,'="',P_EBirth,'",'
                             ,@FEEmail,'="',P_EEmail,'",'
                             ,@FEPSWD,'="',P_EPSWD,'"');
      Set @s := concat('UPDATE tblemployees SET ',@Fields,' WHERE ',@FID,'=',P_ID,';');

Wanneer ik bij de aanroep van de procedure het veld 'P_Ebirth' meegeef werkt dit perfect.... Wens ik bv. dat een medewerker zijn geboortedatum nadien leeg laat, en dus null is, krijg ik een error op deze waarde (null) ...

Bij een insert into heb ik met verschillende sub-cases gewerkt om te bepalen of Birth al dan niet null was en dusdanig een andere query kunnen bouwen...

Bij een update statement is het de bedoeling dat hij alle velden gaat updaten.....

Iemand enig idee wat hier mis mee? (heb nog andere velden ook waar bv. null kan instaan, ook hier lukt het niet mee....)

[Creepy]

Wat voor error krijg je precies en op elke regel?

Gaat de concat niet gewoon mis met een NULL value? Je bent een query string aan het concatten waardoor het wat wordt gerommeld met variabelen e.d.

[dj_vibri]

Inderdaad, hij geeft een fout bij de NULL value maar het moet toch op één-of-andere manier mogelijk zijn om een update van een record te doen waar je de bestaande waarde uithaalt en vervangt door NULL?

Bv: ID - Naam - Adres - Geboortedatum

Record A: 1 - Dj vibri - Leuven - 1985/01/21
Ik voer een update query uit alla:

code:

update tblemployee SET Geboortedatum = null where ID = 1;

Wanneer ik dit MANUEEL doe in de query browser zal het inderdaad lukken, wanneer ik met de SP dit wens te bereiken krijg ik volgende melding:

code:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Haal ik daarintegen het veld 'Birth' uit de string lukt het wel correct... (weliswaar zonder dat het veld geboortedatum aangepast wordt )

Heb ook al geprobeerd om de " en " bij dit veld weg te halen maar tevergeefs.... Wanneer ik debug door het volgende uit te voeren:

code:

Select @Fields;

Wordt hier een NULL ingeplaatst.... dus ben ik vrij zeker dat de update niet kan voldaan worden omdat je dan dit leest:

code:

UPDATE tblEmployees SET NULL WHERE ID = 1;

wat natuurlijk niet correct is....

[Creepy]

En wat zie je als je de query afdrukt of ergens laat loggen? Als ik je code zo eens bekijk dan krijg je iets als

code:

geboorteDatum = "NULL"

wat me zoiezo al niet de bedoeling lijkt. Kan je de variabelen in je SP niet direct in je query gebruiken i.p.v. alles in en string te concatten en die uit te voeren?

[dj_vibri]

Creepy schreef op vrijdag 21 augustus 2009 @ 15:09:
En wat zie je als je de query afdrukt of ergens laat loggen? Als ik je code zo eens bekijk dan krijg je iets als

Wanneer ik in de query browser de output van enerzijds mijn @Fields en anderzijds mijn @s laat printen krijg ik in beide gevallen de waarde 'NULL' terug.... Zodra ik een NULL value gebruik, plaatst hij blijkbaar op de een of andere manier mijn variabele @Fields op NULL.......

Creepy schreef op vrijdag 21 augustus 2009 @ 15:09:
wat me zoiezo al niet de bedoeling lijkt. Kan je de variabelen in je SP niet direct in je query gebruiken i.p.v. alles in en string te concatten en die uit te voeren?

Mijn SP bestaat nu uit 168 lijnen waar ik steeds dezelfde logica in hanteer... nl. mijn @Fields zet bij insert-statements enkel een opsomming van mijn velden (@FEID,@FEFN,.... enz) en deze roep ik dan op in mijn insert statement

vb:

code:

 Set @Fields := concat(@FEID,','
                               ,@FEFN,','
                               ,@FELN,','
                               ,@FEAddress,','
                               ,@FEZip,','
                               ,@FECity,','
                               ,@FEBirth,','
                               ,@FEEmail,','
                               ,@FEPhone,','
                               ,@FEPSWD);

          Set @s := concat('INSERT INTO tblemployees (',@Fields,') values ("',P_EID,'","',P_EFN,'","',P_ELN,'","',P_EAddress,'","',P_EZip,'","',P_ECity,'","',P_EBirth,'","',P_EEmail,'","',P_EPhone,'","',P_EPSWD,'")');

en dit werkt perfect...

Voor een update statement ziet de opbouw van het statement er natuurlijk anders uit en wou via deze @Fields variabele ook kunnen werken... kwestie van het makkelijk te houden

[cariolive23]

Offtopic: Bovenstaande code ziet eruit als een kandidaat voor SQL injection. De inputparameters worden zonder enige beveiliging in een string gezet, @Fields, die vervolgens wordt uitgevoerd. Zie http://www.freelists.org/...-Procedures-SQL-Injection voor een voorbeeldje waarmee het wordt aangetoond.

In PostgreSQL ben je verplicht om voor dynamische queries binnen een stored procedure, wat je hier ook doet, de functies quote_ident (variabele tabel- en kolomnamen) en quote_literal (de content) te gebruiken. Met deze functies wordt de input op de juiste wijze geescaped en wordt SQL injection keurig de nek omgedraaid. Sinds versie 8.4 is er de mogelijk EXECUTE .... USING bijgekomen waarmee je dynamische content veilig in de query kunt zetten.

Ik mag hopen dat MySQL soortgelijke functies heeft, anders heb je een serieus probleem bij dynamische queries. Ik kan je dan ook aanraden om even uit te zoeken welke risico's je loopt, het ziet er niet goed uit.

[dj_vibri]

cariolive23 schreef op vrijdag 21 augustus 2009 @ 15:35:

Ik mag hopen dat MySQL soortgelijke functies heeft, anders heb je een serieus probleem bij dynamische queries. Ik kan je dan ook aanraden om even uit te zoeken welke risico's je loopt, het ziet er niet goed uit.

MySQL heeft inderdaad deze functies met zelfde syntax: EXECUTE *prepared statement* USING .....

Ik ben nog maar in test fase, maar sowieso zal er gebruik gemaakt worden van dit statement. Was hier mee begonnen maar vondt zo direct niet hoe ik dat dynamisch kan maken....

bv voor een insert waar de birth = NULL wordt deze gemaakt:

@S := insert into TblEmployees (Naam) values (?);

maar voor een met birth is het:

@S := insert into TblEmployees (Naam,Birth) values (?,?);

vervolgens is het:

Prepare stmt from @s
EXECUTE stmt USING @a

OF EXECUTE stmt USING @a,@b

Alleen heb ik nog niet gevonden hoe ik die 'execute' slechts 1x onderaan mijn stored procedure moet plaatsen om toch alle mogelijkheden op te vangen..... Tenzei het niet anders kan zal ik het statement in elke 'where' moeten uitvoeren met de specifieke variablen.....

[cariolive23]

Wanneer de procedure een vast aantal IN-parameters heeft, die al dan niet een NULL-waarde hebben, dan kun je toch één statement schrijven met placeholders en deze dan met EXECUTE ... USING ... uitvoeren? Het lijkt mij niet nodig om eerst per parameter uit te gaan zoeken welke waarde deze heeft. Of mis ik iets?

[dj_vibri]

cariolive23 schreef op vrijdag 21 augustus 2009 @ 15:54:
Of mis ik iets?

Het kan altijd zijn dat ik iets mis hoor

maar voor zover ik heb kunnen testen geef je het aantal parameters (bij insert into statement bv. de values) adhv ' ? ' in, en dit aantal ? komt terug bij EXECUTE.....USING..... dus DENK ik dat het wel op voorhand zal moeten kijken of het überhaupt over een " insert/update/delete " actie gaat en vervolgens controleren welke waarden 'null' hebben....

but I'll give it a try (na een feestend week-endje )....