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

  • Css grid

    Geschreven door: op dinsdag 20 februari 2018

    Css grid is een vernieuwende manier voor het indelen van content op website pagina’s. Met behulp van css wordt de uiterlijk van een website bepaald. Doormiddel van een grid is het mogelijk om gemakkel ...

    Bekijk het artikel »
  • CSS vendor prefixes

    Geschreven door: op dinsdag 20 februari 2018

    Bij CSS is het voor een aantal stijl regels mogelijk om aan te geven in welke browser deze gebruikt moet worden. Hiervoor wordt het type browser toegevoegd voor de stijl regel. Dit wordt een prefix (o ...

    Bekijk het artikel »
  • CSS auto prefixer

    Geschreven door: op dinsdag 20 februari 2018

    In een eerder blog over vendor prefixes was te zien hoe met behulp van prefixes experimentele stijling regels voor specifieke browser toegepast kunnen worden. Hoewel het gebruik van prefixes in deze ...

    Bekijk het artikel »
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

whitepaper-customer-selfservice-1.png

Bedien uw klanten optimaal met customer selfservice

Download de whitepaper