Wie soll ich einen Tabellennamen in einen gespeicherten Proc übergeben?

Ich bin gerade in eine seltsame Sache … es gibt einige Code auf unserer Website, die eine riesige SQL-statement, die Änderung es in Code, indem Sie einige suchen und replace auf der Grundlage von einigen Benutzer-Werte, und dann übergeben sie auf SQL server als eine Anfrage.

Ich dachte, das wäre sauberer als parametrisierte Abfrage zu einem gespeicherten Proc, mit den Benutzerwerten als die Parameter, aber wenn ich genauer hinschaute, sehe ich, warum sie es tun könnten … der Tisch, den sie auswählen, ist variabel abhängig von diesen Nutzwerten.

Zum Beispiel, in einem Fall, wenn die Werte waren ("FOO", "BAR") die Abfrage würde am Ende wird so etwas wie "SELECT * FROM FOO_BAR"

Gibt es einen einfachen und klaren path, dies zu tun? Alles, was ich versuche, scheint unelegant zu sein.

EDIT: Ich könnte natürlich den Sql dynamisch in den gespeicherten Proc auslösen und das (bleh) ausführen, aber an diesem Punkt frage ich mich, ob ich etwas gewonnen habe.

EDIT2: Refactoring der Tabellennamen in einer intelligenten Art und Weise, sagen, dass sie alle in einer Tabelle mit den verschiedenen Namen als eine neue Spalte wäre ein schöner path, um all dies zu lösen, die mehrere Menschen haben direkt darauf hingewiesen oder angedeutet. Leider ist es in diesem Fall keine Option.

Zuerst sollten Sie NIEMALS SQL-Befehls-Kompositionen auf einer Client-App wie dieses tun, das ist, was SQL Injection ist. (Sein OK für ein Admin-Tool, das keine Privaten für sich hat, aber nicht für eine gemeinsame Nutzung Anwendung).

Zweitens, ja, ein parametrierter Aufruf zu einem gespeicherten Verfahren ist sowohl sauberer als auch sicherer.

Jedoch , da Sie Dynamic SQL verwenden müssen, um dies zu tun, möchten Sie immer noch nicht die übergebene characterfolge in den Text der ausgeführten Abfrage aufnehmen. Stattdessen möchten Sie die übergebene characterfolge verwenden, um die Namen der tatsächlichen Tabellen nachzuschlagen, die der Benutzer in der Weise abfragen darf.

Hier ist ein einfaches naives Beispiel:

CREATE PROC spCountAnyTableRows( @PassedTableName as NVarchar(255) ) AS -- Counts the number of rows from any non-system Table, *SAFELY* BEGIN DECLARE @ActualTableName AS NVarchar(255) SELECT @ActualTableName = QUOTENAME( TABLE_NAME ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @PassedTableName DECLARE @sql AS NVARCHAR(MAX) SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';' EXEC(@SQL) END 

Manche haben ziemlich gefragt, warum das sicherer ist. Hoffentlich können kleine Bobby Tables das klarer machen:

Alt-Text


Antworten auf weitere Fragen:

  1. QUOTENAME allein ist nicht garantiert sicher zu sein. MS ermutigt uns, es zu benutzen, aber sie haben nicht die Garantie gegeben, dass es nicht von Hackern enttäuscht werden kann. FYI, echte security geht es um die Garantien. Der Tisch-Lookup mit QUOTENAME, ist eine andere Geschichte, es ist unzerbrechlich.

  2. QUOTENAME ist für dieses Beispiel nicht unbedingt notwendig, die Lookup-Übersetzung auf INFORMATION_SCHEMA allein reicht normalerweise aus. QUOTENAME ist hier, weil es gute Form in security ist, eine komplette und korrekte Lösung einzuschließen. QUOTENAME hier drüben eigentlich gegen ein bestimmtes, aber ähnliches mögliches Problem wie Latentinjektion .

(Un) Glücklicherweise gibt es keine Möglichkeit, dies zu tun – Sie können nicht verwenden Tabellenname als Parameter auf gespeicherten Code anders als für dynamische SQL-Generierung übergeben. Wenn es darum geht zu entscheiden, wo zu produzieren sql Code, ich bevorzuge Anwendungscode eher, dass gespeicherten Code. Anwendungscode ist in der Regel schneller und einfacher zu pflegen.

Für den Fall, dass Sie die Lösung nicht mögen, mit der Sie arbeiten, würde ich eine tiefere Neugestaltung vorschlagen (dh das Schema / Anwendungslogik ändern, damit Sie den Tabellennamen nicht mehr als Parameter übergeben müssen).

Ich würde gegen die dynamische Erstellung der SQL in der gespeicherten proc argumentieren; das wird dich in Schwierigkeiten bringen und könnte eine Inflationsanfälligkeit verursachen.

Stattdessen würde ich alle Tabellen parsing, die von der Abfrage betroffen sein könnten und eine Art Aufzählung erstellen, die bestimmen würde, welche Tabelle für die Abfrage verwendet werden soll.

Klingt wie du wäre besser mit einer ORM-Lösung.

Ich kralle, wenn ich dynamische sql in einer gespeicherten Prozedur sehe.

Eine Sache, die Sie betrachten können, ist, eine Fallanweisung zu machen, die denselben SQL-Befehl enthält, den Sie einmal für jede gültige Tabelle wünschen, dann als String den Tabellennamen in diese Prozedur übergeben und den Fall wählen, welcher Befehl ausgeführt werden soll.

By the way als securitysperson der Vorschlag oben sagen Sie aus den Systemtabellen zu wählen, um sicherzustellen, dass Sie eine gültige Tabelle scheint wie eine verschwendete Operation für mich. Wenn jemand den QUOTENAME () ausgeben könnte, dann würde die Injektion genauso gut funktionieren wie auf der zugrunde liegenden Tabelle. Das einzige, was dies hilft, um sicherzustellen, dass es ein gültiger Tabellenname ist, und ich denke, der Vorschlag oben ist ein besserer Ansatz, da Sie nicht mit QUOTENAME () überhaupt.

Je nachdem, ob der Satz von Spalten in diesen Tabellen gleich oder verschieden ist, würde ich es in zweierlei Hinsicht auf längere Sicht ansprechen:

1) Wenn sie das selbe sind, warum nicht eine neue Spalte erstellen, die als Selektor verwendet würde, deren Wert aus den vom Benutzer bereitgestellten Parametern abgeleitet wird? (ist es eine leistungsoptimization?)

2) wenn sie anders sind, sind die Chancen, dass die Handhabung von ihnen auch anders ist. Als solches scheint es, wie man den Select / Handle-Code in einzelne Blöcke aufteilt und dann separat anruft, wäre ein modularer Ansatz für mich. Sie werden das "select * from" -Teil wiederholen, aber in diesem Szenario ist der Satz von Tabellen hoffentlich endlich.

Erlauben Sie dem Anrufcode, zwei beliebige Teile des Tabellennamens zu versorgen, um eine Auswahl zu verwenden, fühlt sich sehr gefährlich an.

Ich kenne den Grund nicht, warum du die data über mehrere Tische verbreitet hast, aber es klingt, als hättest du eine der Grundlagen. Die data sollten in den Tabellen liegen, nicht als Tabellennamen.

Wenn die Tabellen mehr oder weniger das gleiche Layout haben, sollten Sie sich vorstellen, ob es am besten wäre, die data in einer einzigen Tabelle zu setzen. Das würde Ihr Problem mit der dynamischen Abfrage lösen, und es würde das databaselayout flexibler machen.

Anstatt die Tabellen auf der Grundlage von Benutzereingaben auszuwählen, können Sie stattdessen die Prozedur auswählen. das heißt
1. Erstellen Sie eine Prozedur FOO_BAR_prc und drin, dass Sie die Abfrage "select * from foo_bar" setzen, so wird die Abfrage von der database vorkompiliert.
2. Dann basiert auf der Benutzereingabe nun die korrekte Prozedur aus deinem Anwendungscode.

Da hast du rund 50 Tische, das wäre vielleicht keine machbare Lösung, denn es würde viel Arbeit von deinem Teil erfordern.

In der Tat wollte ich wissen, wie man Tabellennamen übergeben, um eine Tabelle im gespeicherten Prozedur zu erstellen. Indem ich einige der Antworten lese und irgendeine Abänderung an meinem Ende versuche, konnte ich endlich eine Tabelle mit dem Namen als Parameter übergeben. Hier ist die gespeicherte Prozedur für andere, um irgendwelche Fehler in ihm zu überprüfen.

USE [databasename] GO / ****** object: StoredProcedure [dbo] [sp_CreateDynamicTable] Skript date: 06/20/2015 16:56:25 ****** / SET ANSI_NULLS EIN GEHEN EINSTELLEN QUOTED_IDENTIFIER EIN GO CREATE PROCEDURE [dbo] [sp_CreateDynamicTable] @tName varchar (255) AS BEGINNEN NOCOUNT EIN; DECLARE @SQL nvarchar (max)

 SET @SQL = N'CREATE TABLE [DBO].['+ @tName + '] (DocID nvarchar(10) null);' EXECUTE sp_executesql @SQL 

ENDE

@RBarry Young Du brauchst die Klammern nicht zu @ActualTableName in der Abfragezeichenfolge hinzuzufügen, da sie bereits im Ergebnis aus der Abfrage in den INFORMATION_SCHEMA.TABLES enthalten ist. Andernfalls gibt es Fehler (s), wenn sie ausgeführt werden.

CREATE PROC spCountAnyTableRows (@PassedTableName als NVarchar (255)) AS – Zählt die Anzahl der Zeilen aus einer Nicht- Systemtabelle , SAFELY BEGIN DECLARE @ActualTableName AS NVarchar (255)

 SELECT @ActualTableName = QUOTENAME( TABLE_NAME ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @PassedTableName DECLARE @sql AS NVARCHAR(MAX) --SELECT @sql = 'SELECT COUNT(*) FROM [' + @ActualTableName + '];' -- changed to this SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';' EXEC(@SQL) 

ENDE