Me encontré en una cosa extraña…hay algo de código en nuestro sitio que está teniendo un gigante de la instrucción SQL, la modificación en el código haciendo algunas buscar y reemplazar basado en algunos de los valores de usuario y, a continuación, pasar a SQL Server como una consulta.

Estaba pensando que esto sería más limpio como una consulta con parámetros a un procedimiento almacenado, con el usuario a los valores de los parámetros, pero cuando miré más de cerca veo por qué podrían estar haciendo…la tabla que se selecciona es variable, depende de los valores de usuario.

Por ejemplo, en un caso, si los valores eran («FOO», «BAR») la consulta termina siendo algo así como «SELECT * FROM FOO_BAR»

Hay una forma clara y sencilla para hacer esto? Todo lo que estoy tratando parece poco elegante.

EDICIÓN: yo podría, por supuesto, generar dinámicamente el sql en el procedimiento almacenado, y exec que (bleh), pero en ese momento me pregunto si he ganado algo.

EDIT2: de Refactorización de la tabla de nombres en cierta manera inteligente, dicen tener todos ellos en una sola tabla con los diferentes nombres como una nueva columna sería una buena forma de solucionar todo esto, que varias personas han señalado directamente, o aludido. Lamentablemente, no es una opción en este caso.

SQL-Server 2008 permite que las variables de TABLA.
Cierto, sí, pero creo (puedo estar equivocado) que funcionan de manera distinta que eso. Una variable de tabla almacena los datos de la tabla, no en los nombres de tabla. Es un concepto similar al de una tabla temporal.
Usted no tiene que «refactorizar los nombres de la tabla», que ya están en el sistema suministrado de vista: INFORMATION_SCHEMA.TABLAS.

OriginalEl autor Beska | 2009-08-07

10 Comentarios

  1. 39

    Primero de todos, usted debe NUNCA hacer comando SQL composiciones en un cliente de la aplicación como esta, que lo que la Inyección de SQL es. (Su autorización para que una herramienta de administración que no tiene privs de su propio, pero no para un uso compartido de la aplicación).

    En segundo lugar, sí, una parametrización de la llamada a un procedimiento Almacenado es tanto más limpio y más seguro.

    Sin embargo, ya que será necesario el uso de SQL Dinámico para hacer esto, usted todavía no desea incluir la cadena que se pasa en el texto de la consulta ejecutada. En su lugar, desea utilizar la cadena que se pasa a buscar los nombres de los real tablas que el usuario podrá consultar en el camino.

    Aquí es un simple ingenuo ejemplo:

    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

    Algunos tienen bastante preguntó por qué esto es más seguro. Esperemos que, poco a Bobby Tablas puede hacer esto más claro:

    ¿Cómo debo pasar un nombre de tabla en un procedimiento almacenado?


    Respuestas a más preguntas:

    1. QUOTENAME solo no garantiza que sea seguro. MS nos anima a usarlo, pero no han dado una garantía de que no se fuera foxed por los hackers. Para su INFORMACIÓN, la Seguridad real es todo acerca de las garantías. La búsqueda de la tabla con QUOTENAME, es otra historia, que es inviolable.

    2. QUOTENAME no es estrictamente necesario para este ejemplo, la Búsqueda de traducción en INFORMATION_SCHEMA solo es normalmente suficiente. QUOTENAME está aquí porque es una buena forma de seguridad para incluir una completa y correcta solución. QUOTENAME aquí es en realidad la protección contra distintos, pero similares potencial problema de saber como latente de inyección.

    Aunque no me gusta el uso de SQL dinámico, esto parece la mejor opción para solucionar el problema a la mano. +1
    Lo siento, no puedo ¿por qué es más SEGURO? Más seguro en qué sentido? No hay ninguna garantía de que la tabla existe, es decir, todavía puede obtener un error de sintaxis cuando se hace EXEC(@SQL).
    Hmm…no sé acerca de su primera declaración…tal vez usted está utilizando una definición diferente para la Inyección de SQL que yo. Ciertamente, si yo tomo esa opción, yo sería «inyectar» texto en una consulta de SQL Server, pero lo estoy usando en el pejoritive sentido de que un usuario modifica algo a propósito para enviar algo a SQL que no debería ser enviado. No tengo que preocuparme por eso, ya que el usuario nunca ve o tiene acceso a cualquiera de los parámetros que habría de ser enviado.
    Ver mi comentario sobre AlexS la respuesta.
    Oh, sí…me encanta el Pequeño Bobby Tablas. Había que uno publicado en mi cubo durante bastante tiempo.

    OriginalEl autor RBarryYoung

  2. 5

    (Onu), afortunadamente, no hay manera de hacer esto – usted no puede usar el cuadro nombre se pasa como parámetro almacenado código distinto para la dinámica de la generación de sql. Cuando se trata de decidir dónde generar código sql, prefiero el código de la aplicación en lugar de que el código almacenado. El código de la aplicación en general es más rápida y más fácil de mantener.

    En caso de que a usted no le gusta la solución que usted está trabajando, me gustaría sugerir un profundo rediseño (es decir, cambiar el esquema de/aplicación de la lógica, de modo que no pasen el nombre de la tabla como un parámetro en cualquier lugar).

    Bueno, no es que no me gusta tanto como me fue con la esperanza de que había una mejor manera de hacerlo. Si la solución actual (modificación de una cadena sql en el código) es la mejor manera, entonces, si quiero ser un buen programador, he tenido mejor así, ¿verdad?
    AlexS: Lo que estamos recomendando es la Inyección de SQL.
    Yo recomiendo un «más profundo rediseño … así que ya no tienes que pasar un nombre de tabla como un parámetro». Esto no es ‘la Inyección de SQL’, supongo 😉
    El uso de SQL dinámico no es ‘la inyección de SQL, es simplemente un método propensos «inyección SQL» que tiene que hacer con cuidado.
    AlexS: «Cuando se trata de decidir dónde generar código sql, prefiero el código de la aplicación en lugar de que el código almacenado» era mi preocupación. «Más profundo rediseño», es, por supuesto, una excelente sugerencia.

    OriginalEl autor AlexS

  3. 2

    Me gustaría argumentar en contra de la dinámica de generación de SQL en el procedimiento almacenado; que voy a meter en problemas y podría causar una vulnerabilidad de inyección.

    Lugar, me gustaría analizar todas las tablas que podrían ser afectadas por la consulta y crear algún tipo de enumeración que determinar qué tabla usar para la consulta.

    Pensé acerca de esto, pero el problema es que hay muchos (alrededor de 50) de las tablas que esto podría suceder en contra, pero quizás de manera más crítica, más que se añade de forma periódica.
    Yo no estoy demasiado preocupado acerca de la inyección de SQL, ya que nunca estamos consultando el usuario para cualquier cosa que pueda estar pasando en el parámetro…son todos los valores que un usuario no tiene acceso. Aún así, mejor que la gente que tengo probablemente pensaban que estaban a salvo cuando ellos no estaban, por lo que la advertencia está bien tomada.
    Él ya tiene a la Inyección de SQL en el código de cliente. No es más seguro que hay en el Servidor SQL server. Y es perfectamente posible utilizar SQL dinámico, incluyendo para este problema, sin ningún tipo de inyección.
    Parece que @RBarry Jóvenes de la respuesta puede ser la mejor apuesta. Es SQL dinámico, pero seguro lo suficiente como para dejar de determinar el nombre de la tabla.

    OriginalEl autor Randolpho

  4. 2

    Parece que estaría mejor con un ORM solución.

    Me estremezco cuando veo sql dinámico en un procedimiento almacenado.

    Como hacemos todos…
    Es perfectamente normal, SI de hacerlo correctamente.
    Algunas veces es inevitable, como cuando se utiliza el PIVOTE de comandos.
    A la derecha, pero en general la forma en que usted encuentra que usted no lo hace correctamente, es que si usted hackeado.
    Sí, es perfectamente bien, si se hace de forma segura. Usted debe todavía se estremece cuando lo vea. Si usted lo está haciendo, las probabilidades son que usted podría haber diseñado un sistema mejor.

    OriginalEl autor ScottE

  5. 1

    Una cosa que usted puede considerar es la de hacer una declaración que contiene el mismo comando SQL que desea, una vez para cada válido de la tabla, a continuación, pasar como un string el nombre de la tabla en este procedimiento y en el caso de elegir la que comando a ejecutar.

    Por la manera como una seguridad de la persona a la sugerencia de arriba diciendo que seleccionar una de las tablas del sistema para asegurarse de que usted tiene una tabla válida parece un desperdicio de operación a mí. Si alguien puede inyectar pasado el QUOTENAME (), a continuación, luego de la inyección que trabajan en el sistema de mesa tan bien como en la tabla subyacente. La única cosa que esta ayuda con ella para asegurarse de que es válido nombre de la tabla, y creo que la sugerencia de arriba es un mejor enfoque de la que ya no está utilizando QUOTENAME ().

    Esta última observación está mal. La consulta que RBarryYoung escribió NO es dinámica y no hacer las actualizaciones y no llena ninguna de las variables con que no son de confianza de los datos. Por lo tanto, existe el riesgo CERO de directo, latente o persistente de inyección de SQL. Muy diferente de hacer EXEC(‘UPDATE’ + @tablename + ‘…’). No solo no hay riesgo, pero es el elemento clave que hace que el pleno cosa segura.

    OriginalEl autor pilavdzice

  6. 0

    Dependiendo de si el conjunto de columnas en las tablas es la misma o diferente, me gustaría acercarse a él de dos maneras en el largo plazo:

    1) si el mismo, ¿por qué no crear una nueva columna que sería utilizado como un selector, cuyo valor se deriva de los parámetros proporcionados por el usuario ? (se trata de una optimización del rendimiento?)

    2) si son diferentes, es probable que el manejo de ellos también es diferente. Como tal, parece que la división de la seleccionar y manejar el código en bloques separados y, a continuación, llamar a ellos por separado sería un mayor enfoque modular para mí. Te voy a repetir el «select * from» parte,
    pero en este escenario el conjunto de tablas es de esperar que finito.

    Permitiendo que el código de llamada para el suministro de dos partes arbitrarias de el nombre de la tabla para hacer una selección de la que se siente muy peligroso.

    Tristemente, mientras que yo podría haber una enumeración para decidir qué tabla para seleccionar, sería muy grande (hay cerca de 50 tablas de ahora), pero lo peor, más que se añade de forma periódica, lo que llevaría a un problema de mantenimiento.
    así que usted tiene más de un caso de (1) en lugar de (2) ? por ejemplo, algo así como una lista de las tablas que contienen «Nombre, Apellido», tablas que se denomina «Programadores», «Administradores», «Directores», «Usuarios», etc. ?

    OriginalEl autor Andrew Y

  7. 0

    No sé la razón por la que tiene los datos repartidos en varias tablas, pero parece que se están rompiendo uno de los fundamentos. Los datos deben estar en las mesas, no como los nombres de tabla.

    Si las tablas tienen más o menos el mismo diseño, considere si sería mejor poner los datos en una sola tabla en su lugar. Que podría resolver su problema con la dinámica de la consulta, y sería hacer el diseño de base de datos más flexible.

    Estoy de acuerdo en la teoría, pero no se puede hacer. Es un Comercio problema de Servidor. No entiendo todos los detalles de la misma, pero es un gran sitio, y las diferentes tablas del catálogo de la parte de ella que está en el núcleo del sitio. No hay manera de que podemos cambiar en el corto plazo.

    OriginalEl autor Guffa

  8. 0

    Lugar de Consultar las tablas basados en los valores de entrada, usted puede elegir el procedimiento en su lugar.
    es decir

    1. Crear un procedimiento FOO_BAR_prc y dentro de que usted ponga la consulta «select * from foo_bar’ , de esa manera, la consulta será precompilado por la base de datos.

    2. Luego, en función de la entrada del usuario, ahora ejecutar el procedimiento correcto desde el código de la aplicación.

    Ya que tiene alrededor de 50 mesas, esto podría no ser una solución viable, aunque como se requeriría de mucho trabajo de su parte.

    Bueno, yo no tengo miedo de un montón de trabajo si es un trabajo a tiempo, pero el mantenimiento de 50 almacenados procs si era necesario, dicen, agregar una columna a la consulta, sería una pesadilla.

    OriginalEl autor Rajat

  9. 0

    De hecho, yo quería saber cómo pasar el nombre de tabla para crear una tabla en el procedimiento almacenado. Mediante la lectura de algunas de las respuestas y tratando de hacer algunas modificaciones en mi final, que finalmente capaz de crear una tabla con el nombre pasado como parámetro. Aquí está el procedimiento almacenado para otros a revisar cualquier error en ella.

    El USO de [Nombre de Base de datos]
    IR
    /****** Objeto: StoredProcedure [dbo].[sp_CreateDynamicTable] Fecha de Script: 06/20/2015 16:56:25 ******/
    SET ANSI_NULLS ON
    IR
    SET QUOTED_IDENTIFIER ON
    IR
    CREATE PROCEDURE [dbo].[sp_CreateDynamicTable]
    @tName varchar(255)
    COMO
    COMENZAR
    SET NOCOUNT ON;
    DECLARE @SQL de tipo nvarchar(max)

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

    FINAL

    OriginalEl autor Sushil Pugalia

  10. 0

    @RBarry Jóvenes
    No es necesario añadir los soportes a @ActualTableName en la cadena de consulta, porque ya está incluido en el resultado de la consulta en el INFORMATION_SCHEMA.TABLAS. De lo contrario, habrá de error(s) cuando se ejecuta.

    CREATE PROC spCountAnyTableRows( @PassedTableName como NVarchar(255) ) COMO
    — Cuenta el número de filas que de cualquier otro sistema de Mesa, de forma SEGURA
    COMENZAR
    DECLARE @ActualTableName COMO 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)

    FINAL

    OriginalEl autor omostan

Dejar respuesta

Please enter your comment!
Please enter your name here