sql - ¿Cómo se usa cfqueryparam en la cláusula ORDER BY?

coldfusion

Question

<cfqueryparam> ser un buen desarrollador web de CF y uso <cfqueryparam> alrededor de todos los elementos de FORMULARIO o URL que llegan a mis consultas SQL.

En este caso, intento permitir que un usuario controle dinámicamente la cláusula ORDER BY.

<cfquery datasource="MyDSN" name="qIncidents">
  SELECT IncidentID, AnimalID, IntakeDate, DxDate, OutcomeDate
  FROM Incidents
  WHERE ShelterID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.ShelterID#">
  ORDER BY <cfqueryparam cfsqltype="cf_sql_varchar" value="#SortBy#">
</cfquery>

Cuando hago esto, obtengo el siguiente error:

El elemento SELECT identificado por ORDER BY número 1 contiene una variable como parte de la expresión que identifica una posición de columna. Las variables solo se permiten cuando se ordena por una expresión que hace referencia a un nombre de columna.

¿Alguna sugerencia sobre cómo hacer esto de forma segura?

Answer 1

Desafortunadamente, no puede usar CFQUERYPARAM directamente en la cláusula Order By.

Si desea utilizar el Order By de forma dinámica pero aún hacerlo de forma segura, puede configurar un CFSWITCH o una estructura similar para cambiar su variable SortBy dependiendo de alguna condición (por ejemplo, una variable URL). Como siempre, no pase ningún valor directamente del usuario, simplemente mire la entrada del usuario y seleccione de una lista predeterminada de valores posibles en función de eso. Entonces, solo usa la sintaxis estándar:

ORDER BY #SortBy#

Answer 2

En cuanto al comentario sobre el uso de "cfqueryparam en la cláusula de orden fina con MySQL". Sí, creo que está permitido con fuentes de datos MySQL. Aunque usa el ordinal de columna, no el nombre de columna (que parece tratarse como una cadena constante en su lugar).

Desafortunadamente, no parece funcionar en absoluto para los datasources de MS SQL. Al menos no por lo que puedo decir.

<!--- this works --->
<cfset url.sortColumnNumber = "3">
<cfquery name="getDataByPosition" datasource="MySQLDSN">
   SELECT  RecordID, ProductName, DateAdded
   FROM TestTable
   ORDER BY <cfqueryparam value="#url.sortColumnNumber#" cfsqltype="cf_sql_integer"> ASC
</cfquery>
<cfdump var="#getDataByPosition#">

<!--- this does NOT work --->
<cfset url.sortColumnName = "DateAdded">
<cfquery name="getDataByName" datasource="MySQLDSN">
   SELECT  RecordID, ProductName, DateAdded
   FROM    TestTable
   ORDER BY <cfqueryparam value="DateAdded" cfsqltype="cf_sql_varchar"> ASC
</cfquery>
<cfdump var="#getDataByName#">

Actualización: Respecto a los comentarios sobre el ordinal: No, creo que se refiere a la posición de la columna en la lista de selección, no a la tabla subyacente. Entonces debería estar bien.

Sí, estoy de acuerdo en que la protección de inyección sql no es el objetivo principal de cfqueryparam. Entonces la descripción de las variables de vinculación fue una buena adición.

Answer 3

Pensé que lanzaría un código menor a este problema:

<cfset sortColumns = {IncidentID = "IncidentID", AnimalID = "AnimalID", IntakeDate = "IntakeDate", DxDate = "DxDate", OutcomeDate = "OutcomeDate"}>
<cfset sortDirections = {ASC = "ASC", DESC = "DESC"}>

<cfquery datasource="MyDSN" name="qIncidents">
    SELECT IncidentID, AnimalID, IntakeDate, DxDate, OutcomeDate
    FROM Incidents
    WHERE ShelterID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.ShelterID#">
    ORDER BY #sortColumns[sortBy]# #sortDirections[sortDirection]#
</cfquery>

Donde sortBy y sortDirection entran a través de la URL o donde sea.

Me gusta esto porque está limpio y no se puede inyectar nada a través de la cláusula ORDER BY.

¿Algún comentario?

Answer 4

Voy a ampliar la respuesta de Aaron. Una de las cosas que hago es usar listfindnocase () para asegurarme de que los argumentos pasados ​​a la cláusula order by son válidos:

<cfset variables.safeSortColumn = "name">
<cfset variables.safeSortOrder = "desc">

<cfparam name="url.sortcolumn" type="string" default="#variables.safeSortColumn#">
<cfparam name="url.sortorder" type="string" default="#variables.safeSortOrder#">

<cfif listfindnocase("name,age,address", url.sortcolumn)>
    <cfset variables.safeSortColumn = url.sortcolumn>
</cfif>

<cfif listfindnocase("desc,asc", url.sortorder)>
    <cfset variables.safeSortOrder = url.sortorder>
</cfif>

<cfquery>
select *
from mytable
order by #variables.safeSortcolumn# #variables.safeSortorder#
</cfquery>

Answer 5

el problema con el uso del valor ordinal para una referencia de columna es (creo) el valor ordinal en el momento en que se ejecutó la declaración SQL de la tabla de creación, para agregar columnas a la tabla de la base de datos a lo largo del tiempo, la herramienta GUI que usas mostrar las columnas puede no representar su valor ordinal real. Realmente me mantendría alejado de usar cfqueryparam para esto.

ME GUSTA la idea de usar un número en las variables de solicitud (url, formulario) para especificar qué columna ordenar y luego usar eso en el interruptor y traducirlo a un nombre de columna real - para que no expongas los nombres de tus columnas al usuario.

en cuanto a cuándo / por qué usar cfqueryparam, recuerde NO solo sobre la validación de entrada y la prevención de inyección SQL (aunque es una bonificación muy buena) - con cfqueryparam el SQL subyacente a la base de datos se envía de vuelta a través del controlador mediante SQL variables - valores de marcador de posición, por lo que el optimizador de Databse puede determinar qué índice usar en un formato más genérico ... por lo tanto, cuando envíe una declaración SQL como esta: SELECCIONE * FROM producto WHERE ID = 1 y SELECT * FROM producto WHERE ID = 2 el optimizador funciona ambas veces. pero con las variables de vinculación, el SQL se ve así: SELECT * FROM producto WHERE ID =? (? = 1) y SELECCIONAR * FROM producto WHERE ID =? (? = 2) para que el optimizador pueda usar los resultados almacenados en caché del primer análisis para saber exactamente qué índice usar en la segunda consulta. Dependiendo de la complejidad del SQL y de la base de datos, esto puede ser un gran ahorro de tiempo. en mi experiencia, es muy útil en cuanto a rendimiento, con oráculo y columnas de fecha / hora en la cláusula where.

así que en cuanto a dónde usar cfqueryparam, es donde se puede usar una variable de enlace SQL ...

hth jon