Dynamische queries in SQL

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:

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:

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:

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:

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 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:

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:

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