SQL Injection – Schnelltest

15. Dezember 2009 admin Keine Kommentare

Um einen evtl. gefährlichen String zu testen, habe ich nachfolgend einen Schnelltest in Form einer Scalar-Funktion entwickelt.

Diese Funktion übernimmt einen potenziell gefährlichen String und prüft ihn auf die wichtigsten Merkmale.

  • Es dürfen keine String-Hochkommas vorkommen die nicht paarig sind.
  • Desweiteren sind DDL-Kommandowörter als potzenziell gefährlich einzustufen.

Wenn ein “Problem” gefunden wurde, wird eine Fehlermeldung ausgegeben. Im anderen Fall wird der Originalstring als Rückgabewert präsentiert.

Natürlich erhebt diese Funktion keinen Anspruch auf Vollständigkeit, jedoch ist sie gerade bei der Entwicklung generischer (metadatengesteuerter) Prozeduren für den schnellen Test gerade dieser Metatabellen sehr nützlich.

Mehr…

KategorienSQL-Server 2005

TSQL Fehler bei Case (simple Version)

3. Dezember 2009 admin Keine Kommentare

Die Case-Anweisung von T-SQL verhält sich sehr interessant (fehlerhaft?) bei Ausdrücken die in der einfachen Version des Statements in der Case-Expression angegeben werden.

Die einfache Syntax siehe wie folgt aus:

 T-SQL |  copy code |? 
1
CASE input_expression
2
     WHEN when_expression THEN result_expression
3
    [ ...n ]
4
     [
5
    ELSE else_result_expression
6
     ]
7
END

Um das Problem zu demonstrieren, möchte ich einmal das folgende Statement zur Diskussion stellen:

 T-SQL |  copy code |? 
01
DECLARE @i INT
02
 
03
SET @i = 0
04
 
05
WHILE @i<10
06
BEGIN
07
	PRINT
08
	CASE CONVERT(INT,RAND()*5+1)
09
		WHEN 1 THEN 10
10
		WHEN 2 THEN 20
11
		WHEN 3 THEN 30
12
		WHEN 4 THEN 40
13
		WHEN 5 THEN 50
14
		WHEN 6 THEN 60
15
		ELSE 0
16
	END
17
	SET @i = @i + 1
18
END

Das CASE-Statement verwendet die Random-Funktion RAND().

Diese Funktion liefert Werte zwischen 0 und 1 zurück. Multipliziert man nun den Rückgabewert mit 5 sollten also bis dahin Zahlen zwischen 0 und 5 zurückgeliefert werden. Um 0-Werte auszuschließen wird abschließend noch 1 dazu addiert und der endgültige Wertebereich liegt zwischen 1 und 6.

Soweit so gut.

Mehr…

KategorienSQL-Server 2005

SQL Server: Index Selektivität und UnUsed Indizes

11. November 2009 admin Keine Kommentare

In einer Datenbank können sich über die Zeit sehr viele Tabellen, und damit verbunden auch sehr viele Indizes ansammeln. Möchte man nun wissen welche dieser Indizes noch in Gebrauch sind, kann man dies mittels der Abfrage , wie sie im Beispiel unten gezeigt wird, ermitteln.

In der Dynamic-Management-View sys.dm_db_index_usage_stats sammelt SQL Server Statistikdaten über die Verwendung eines jeden Indexes der auf der SQL Server Instanz angesprochen wird.

Mehr…

KategorienSQL-Server 2005

SET OPTIONs abfragen und SSIS-Probleme vermeiden

22. Oktober 2009 admin Keine Kommentare

Im vorliegenden Fall hatte ich ein Problem mit einem SSIS-Paket dass mit einer Fehlermeldung abgebrochen wurde. Die Fehlermeldung besagte, dass es eine Warnung beim Aufruf der Datenquelle (View) gegeben hätte.

Diese Warnung sollte nach meiner Meinung auch als Warnung und nicht als Fehler behandelt werden. Das Problem war leicht zu beheben, ich musste nur ein Set-Kommando vor der View platzieren dass die ANSI_WARNINGS abschaltet.

 T-SQL |  copy code |? 
1
SET ANSI_WARNINGS OFF

Der View Aufruf liefert jetzt den Returncode 0 zurück und das Paket tut das was es immer hätte tun sollen.

Im Nachhinein stellte sich mir jedoch die Frage, welche SET-Options denn eigentlich für eine aktuell ausgeführte Session gerade aktiv sind und wie diese abgefragt werden können?

Mehr…

KategorienSQL-Server 2005

Funktionsergebnisse mit cross/outer apply einbinden

21. Oktober 2009 admin Keine Kommentare

Ich möchte heute eine interessante Methode vorstellen die es ermöglicht die Ergebnisse von Funktionen in SQL-Abfragen zu verwenden.

Die sogenannten Table-Values-Functions (Funktionen die eine Tabelle als Return-Wert haben) können bei Abfragen wie eine Tabelle in ein Select-Statement eingebunden werden. Parameterwerte dürfen jedoch bei einem normalen Join nur von außerhalb des Select-Statements versorgt werden.

Als Beispiel könnte die in der AdventureWorks enthaltene Funktion dbo.ufnGetContactInformation in einer Abfrage verwendet werden. Als Parameterwert kann die ContactID einer Person übergeben werden und die TVF (Table-Valued-Function) liefert ein paar Kontaktdaten (Name, Vorname, Position, KontaktArt) zurück.

Möchte man diese Funktionalität nun in einem Select verwenden, ist man versucht die TVF, die ja wie eine Tabelle behandelt werden kann, wie folgt einzusetzen:

Nicht Korrekt!

 T-SQL |  copy code |? 
01
WITH Liste AS
02
(
03
    SELECT   SalesPersonID
04
            ,SUM(TotalDue) AS TotalDue
05
    FROM Sales.SalesOrderHeader
06
    GROUP BY  SalesPersonID
07
)
08
SELECT   ci.FirstName
09
        ,ci.LastName
10
        ,ci.JobTitle
11
        ,ci.ContactType
12
        , l.TotalDue
13
FROM Liste l
14
join HumanResources.Employee e 
15
      ON e.EmployeeID = l.SalesPersonID
16
join dbo.ufnGetContactInformation(e.ContactID) ci 
17
      ON ci.ContactID = e.ContactID

Mehr…

KategorienSQL-Server 2005

Parent-Child Tabellen für Reports sortieren

13. Oktober 2009 admin Keine Kommentare

Wenn man eine Parent-Child-Tabelle für einen Report vorbereiten soll, so ist das nicht immer ganz einfach. Die Parent-Child-Hierarchy kann man zwar noch mit einer rekursiven CTE (common table expression) abfragen, jedoch ist die anschließende Sortierung des Resultsets ein nicht zu unterschätzendes Hindernis.

Die CTE-Abfrage liefert zwar die richtigen Datensätze, gibt sie aber nicht in einer, für den Report geeigneten Weise, zurück.

Beispiel eines Ergebnis-Sets für eine Abfrage der Personal-Hierarchy aus der AdventureWorks:

Title                           EmployeeID  ManagerID    Level
------------------------------- ----------- -----------  ------
Chief Executive Officer         109         NULL         0     
Marketing Manager               6           109          1     
Vice President of Engineering   12          109          1     
Information Services Manager    42          109          1     
Chief Financial Officer         140         109          1     
Vice President of Production    148         109          1     
Vice President of Sales         273         109          1     
North American Sales Manager    268         273          2     
European Sales Manager          284         273          2     
Pacific Sales Manager           288         273          2     
Sales Representative            290         288          3     
Sales Representative            285         284          3     
Sales Representative            286         284          3     
Sales Representative            289         284          3     
Sales Representative            275         268          3  

Mehr…

KategorienSQL-Server 2005

T-SQL Stringhandling (Extraktion von Token)

25. September 2009 admin Keine Kommentare

Heute möchte ich einmal ein kleines Script bzw. eine kleine Funktion vorstellen die zum Zerlegen von Strings sehr nützlich sein kann.

Wenn man strukturierte Texte vorliegen hat und daraus bestimmte Teile extrahieren soll, so kann man dies sicher mit den Stringfunktionen von T-SQL erledigen. Gerade die Verarbeitung von Listen (Kommasepariert oder sonst irgendwie) bedingt aber die wiederholte Extraktion von Textblöcken (Token) die durch verschiedenste Begrenzungszeichen voneinander getrennt sind.

Als Token verstehe ich in diesem Zusammenhang Teile eines Textes die durch Begrenzer (Delimiter) voneinander logisch getrennt werden. In einem Satz wären das die Worte, in einer Liste die durch z.B. Komma getrennten Artikelnummern oder einfach Bestandteile eines Tabellennamens usw.

Sehen wir uns hierzu mal ein paar Beispiele an:

  • String: ‘Hello World, how are you?’
  • String: ‘123,2345,231234,3456′
  • String: ‘[AdventureWorks].[Person]. [Contact]‘

Extrahieren wir mal das dritte Wort aus Beispiel 1, den zweiten Parameterwert aus Beispiel 2 und den Tabellennamen aus Beispiel 3!

Mit Standardmitteln sicherlich ebenfalls zu realisieren, jedoch mit einer Funktion viel eleganter und Übersichtlicher zu gestalten:

Mehr…

KategorienSQL-Server 2005

T-SQL CASE und die implizite Datentypkonvertierung

23. September 2009 admin Keine Kommentare

Wenn man in einem Ausdruck einen CASE-Block verwendet, so ist dies auf den ersten Blick noch keine Besonderheit. Vergisst man jedoch dabei, dass SQL Server eine implizite Datentypkonvertierung vornimmt, so kann dies zu fehlerhaften Ergebnissen führen die noch dazu sehr schwer zu finden sind.

Betrachten wir hierfür einmal folgendes Beispiel:

 T-SQL |  copy code |? 
01
DECLARE @AlteKndNr VARCHAR(15)
02
DECLARE @Von       VARCHAR(15)
03
DECLARE @Auf       VARCHAR(15)
04
DECLARE @Regel     INT
05
 
06
SET @AlteKndNr = 'K011110999-VIP'
07
 
08
SET @Regel = 1
09
SET @Von   = CASE @Regel WHEN 1 THEN '099' WHEN 2 THEN '0111' ELSE 0 END
10
SET @Auf   = CASE @Regel WHEN 1 THEN '033' WHEN 2 THEN '0000' ELSE 0 END
11
 
12
SELECT NeueKndNr = REPLACE(@AlteKndNr, @Von, @Auf), @Von, @Auf
13
 
14
SET @Regel = 2
15
SET @Von   = CASE @Regel WHEN 1 THEN '099' WHEN 2 THEN '0111' ELSE 0 END
16
SET @Auf   = CASE @Regel WHEN 1 THEN '033' WHEN 2 THEN '0000' ELSE 0 END
17
 
18
SELECT NeueKndNr = REPLACE(@AlteKndNr, @Von, @Auf), @Von, @Auf
19
 
20
-- -------------------------------------------------------------------------
21
-- Ergebnisse:
22
--
23
-- NeueKdNr bei @Regel = 1: K011110339-VIP = OK
24
-- NeueKdNr bei @Regel = 2: K0010999-VIP   = FALSCH
25
--                          K000010999-VIP = Das währe richtig!
26
-- -------------------------------------------------------------------------
27




 Mehr…

			

		

		

			KategorienSQL-Server 2005					

	

	

		
‘virtuelle’ Arrays mit T-SQL

		

			22. September 2009
			admin						Keine Kommentare
			

		

		

			

In T-SQL sind per Definition keine Arrays vorgesehen. Es gibt jedoch Fälle in denen der Einsatz eines Arrays von Vorteil wäre.


In einem konkreten Fall soll eine Prozedur einen Datenwert als Parameter erhalten. Dieser Wert wird jedoch in den, für die Abfrage zugrundeliegenden Tabellen nicht direkt verwendet. Vielmehr wird auf den Parameterwert nur indirekt über den Primärschlüssel (IdentityFeld) der Schlüsseltabelle verwiesen.


In der Prozedur müsste jetzt eine Abfrage in der Form:


 T-SQL |  copy code |? 
1
SELECT <feldliste>
2
FROM <komplexe Abfrage>
3
    join Schlüsseltabelle ON Code =
4
<parameterwert></parameterwert></komplexe></feldliste>




erstellt werden.


Grundsätzlich ist dagegen ja nichts einzuwenden. Wenn der Join gegen die Schlüsseltabelle jedoch die Laufzeit des Gesamtstatements spürbar verlängert und zusätzlich in der Prozedur dieser Join mehrfach verwendet wird, dann sollte man darüber nachdenken ob es vielleicht eine performantere Lösung gibt.


Da es kein Array in T-SQL gibt kann man dieses als String simulieren. Dieses virtuelle Array trennt die einzelnen Werte durch einen definierten Delimiter (in meinem Fall ‘|’ der senkrechte Strich). Bei der Abfrage muss jetzt nur der Abfragewert in einen String mit umgebenden Delimitern convertiert werden und über CHARINDEX mit der virtuellen Array-Variablen verglichen werden.


Eine Lösung könnte damit in etwa so aussehen:


 T-SQL |  copy code |? 
1
<erstelle ARRAY mit IdentWerten die durch parameterwert definiert sind>
2
SELECT <feldliste>
3
FROM <komplexe Abfrage>
4
WHERE CHARINDEX('|'+CONVERT(VARCHAR(10),<quellfeld>)+'|',@ARRAY) > 0</quellfeld></komplexe></feldliste></erstelle>




 Mehr…

			

		

		

			KategorienSQL-Server 2005					

	

	

		
Datensatzlänge einer Abfrage schätzen

		

			8. September 2009
			admin						Keine Kommentare
			

		

		

			



Gerade wenn man ein bestehendes Datenbanksystem optimieren möchte, könnte die Frage nach der durchschnittlichen Breite einer Abfrage auftauchen. Speziell wenn man keine Tabelle zur Hand hat die in ihren Properties zumindest den verbrauchten Speicherplatz anzeigt, ist die nachfolgende Prozedur vielleicht ganz nützlich.


Es werden für eine gegebene Abfrage die Ergebnisse in eine temporäre Tabelle geschrieben und dann mittels der Funktion DATALENGTH die tatsächliche Länge eines Feldes bestimmt. Die durchschnittliche Summe der Gesamtlänge eines Datensatzes wird in der Rückgabe angezeigt.


Wenn man diese Durchschnittslänge mit der Anzahl der Datensätze multipliziert, erhält man den Nettowert des Datenvolumens das transferiert wird. (zumindest annähernd;))


Wenn ein Feld einen NULL-Wert enthält, setzt die Prozedur eine Feldlänge von 4 Byte ein. Dies wird als Mittelwert einer leeren Feldlänge verwendet und könnte in einer weiteren Ausbaustufe ggf. noch aus den System-Views für die Feldtypen genauer bestimmt werden.





Die Abfrage sollte eine repräsentative Menge an Daten zurückliefern. Es ist vor allem bei sehr großen Ergebnismengen ein wenig Vorsicht angesagt, um nicht die Temp-Datenbank in Bedrängnis geraten zu lassen ;) 


 Mehr…

			

		

		

			KategorienSQL-Server 2005