Dynamische queries in SQL

Geschreven door: op zaterdag 28 oktober 2017

Leestijd:

In SQL maak je queries meestal op basis van bekende kolommen en tabellen. Je geeft aan welke gegevens je wilt ophalen en waar die gegevens te vinden zijn. In sommige gevallen weet je echter bij het schrijven van een query niet wat de naam van een kolom of tabel is.

In SQL kan je niet verwijzen naar een tabel of kolom als je de naam ervan niet weet en je kunt deze ook niet aanspreken als eigenschap van een object. In Javascript, bijvoorbeeld, kan je nog myObject[propertyName] gebruiken, maar in SQL bestaat er niet zoiets als SELECT properties[propertyName] from tables[tableName].

Dus hoe pak je zoiets aan? Dynamische queries. Het idee is dat je de query als een string opbouwt en deze dan uitvoert. In SQL zijn hiervoor twee methodes beschikbaar: de functie EXEC en de stored procedure sp_executesql.

EXEC

EXEC is de eenvoudigste versie: je maakt een query als string aan en geeft deze op als argument aan de functie. Een voorbeeld:

Eerst maken we een voorbeeldtabel:


CREATE TABLE MijnTable(ID int, Kolom1 nvarchar(255), Kolom2 nvarchar(255));

GO

INSERT INTO MijnTable(ID, Kolom1, Kolom2)
       VALUES
      (1, 'abc', 'xyz'),
      (2, 'klm', 'opr')
;


SELECT * FROM MijnTable;

Resultaat:

sql-result-1.jpg

 

Om een query uit te voeren, maak je een query aan in een string:

 


DECLARE @sql nvarchar(max);

SET @sql = 'SELECT Kolom1 FROM MijnTable WHERE ID = 1';

EXEC(@sql);

Resultaat:

sql-result-2.jpg

In de kolom staat nu expliciet aangegeven welke kolom moet worden geselecteerd en van welke ID, maar het de reden voor dynamische queries is dat deze via variabelen opgegeven worden:


DECLARE @sql nvarchar(max);

DECLARE @columnName nvarchar(255);

DECLARE @ID int;

SET @columnName = 'Kolom2';

SET @ID = 2;

SET @sql = 'SELECT ['+@columnName+'] FROM MijnTable WHERE ID = ' + cast(@ID as nvarchar(10));

EXEC(@sql);

Resultaat:

sql-result-3.jpg

Via de variabelen @columnName en @ID worden de gewenste waardes nu in de query geplaatst. Hierbij valt op:

  1. Om de kolomnaam staan blokhaakjes omdat er niet vanuit kan worden gegaan dat de opgegeven naam zonder spaties is of anderszins correct om te gebruiken zonder blokhaakjes.
  2. De variabele @ID wordt gecast als nvarchar omdat een int niet aan een string geplakt kan worden zonder casten.

Sp_executesql

Dynamische queries worden met EXEC uitgevoerd binnen een eigen context. Dat betekent dat variabelen buiten die context niet beschikbaar zijn. Het volgende is bijvoorbeeld niet mogelijk:


--Voorgaande code

SET @sql = 'SELECT ['+@columnName+'] FROM MijnTable WHERE ID = @ID';

EXEC(@sql);

Het resultaat hiervan is:

sql-result-4.jpg

Om toch gebruik te kunnen maken van queries met parameters, kan je gebruikmaken van sp_executesql. Deze stored procedure verwacht naast de dynamische query ook een string met daarin de definities van de parameters en een lijst met parameters, bijvoorbeeld:


SET @sql = 'SELECT ['+@columnName+'] FROM MijnTable WHERE ID = @ID';

execute sp_executesql @sql, N'@ID int', @ID = @ID

 

Resultaat:

sql-result-5.jpg

SQL Injection

Omdat je gedeeltes van je query niet per se parameters hoeven te zijn, zie @columnName in de voorbeelden, bestaat het gevaar van SQL injection. Als alle variabelen door de ontwikkelaar worden bepaald is dat geen probleem, maar wel als invoer van een gebruiker aan de query wordt toegevoegd. Een voorbeeld:


--Voorgaande code

SET @columnName = 'Kolom2] FROM MijnTable; SELECT NEWID(); SELECT [Kolom2';

SET @sql = 'SELECT ['+@columnName+'] FROM MijnTable WHERE ID = @ID';

execute sp_executesql @sql, N'@ID int', @ID = @ID

Resultaat:

sql-result-6.jpg

 

In bovenstaande voorbeeld wordt slechts een GUID gegenereerd, maar kwaadwillenden kunnen andere commando’s uitvoeren en zo de database of zelfs de hele server corrumperen.

Of natuurlijk een aantal tabellen verwijderen:

Exploits of a Mom

 

Zorg er dus voor dat variabelen die worden gebruikt in dynamische queries altijd zijn gecontroleerd op geldigheid of niet door gebruikers worden bepaald.


Andere blogartikelen

  • Waarom developers het zo leuk vinden bij ons

    Geschreven door: op woensdag 13 november 2019

    Bij ons op kantoor werkt een team van betrokken specialisten, waaronder 4 backend developers en 4 UX developers. Zij zorgen voor een belangrijk fundament binnen dit bedrijf. Een goede reden om te zorg ...

    Bekijk het artikel »
  • Beachvolleyballen voor het goede doel!

    Geschreven door: op vrijdag 25 oktober 2019

    Afgelopen vrijdag 11 oktober  hebben diverse betrokken specialisten uit ons team de handen ineen geslagen met een van onze opdrachtgevers. Samen met De Monchy International B.V. hebben we meegeda ...

    Bekijk het artikel »
  • Het gebruik van een Photoslider

    Geschreven door: op maandag 7 oktober 2019

    Ze zijn er in alle soorten en maten en staan meestal bovenaan een websitepagina. Dan heb ik het natuurlijk over photosliders. Zo bent u vast bekend met de traditionele foto die langzaam vervaagd en wa ...

    Bekijk het artikel »
Bel 072 5345 888
Onze Middelen en Technologieën
microsoft silver partner
Adobe partner
fd-gazellen-2018.jpg
Google analytics
partners-logo.jpg
Op de hoogte blijven?

Meld u aan voor de gratis nieuwsbrief om op de hoogte te blijven van onze activiteiten