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.


Op De Hoogte Blijven?

Online Succes realiseren is een vak, een vak wat wij verstaan en waarover we je graag vertellen. Schrijf je in voor onze maandelijkse nieuwsbrief en blijf op de hoogte van trends, thema’s en succesverhalen.

Aanhef

Andere blogartikelen

Bel 072 5345 888
Meer dan 40 bedrijven vertrouwen op ons
Allrig is de alles in een leverancier binnen de energie-industrie
Aliancys is een toonaangevend wereldwijd bedrijf actief in de verkoop van kwaliteitsharsen
ERIKS is een toonaangevende en innovatieve leverancier aan de procesindustrie en aan machinebouwers, die zowel de rol van specialist als die van brede MRO-leverancier vervult
Industrieel dienstverlener Heinen & Hopman Engineering uit Bunschoten is dé wereldwijde specialist op het gebied van klimaatbeheersing
Handicare is een internationale organisatie die ouderen helpt om hun dagelijks leven gemakkelijker te maken door het produceren van hoogwaardige trapliften
Onze Middelen en Technologieën
microsoft silver partner
Adobe partner
fd gazellen 2017
Google analytics
Google adwords
TelefoonE-mail