Wie verwenden Sie cfqueryparam in der ORDER BY-Klausel?

Question

Ich versuche, ein guter CF-Webentwickler zu sein und verwende <cfqueryparam> um alle FORM- oder URL-Elemente, die in meinen SQL-Abfragen vorkommen.

In diesem Fall versuche ich, einem Benutzer die Möglichkeit zu geben, die ORDER BY-Klausel dynamisch zu steuern.

<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>

Wenn ich dies tue, erhalte ich den folgenden Fehler:

Das SELECT-Element, das durch die ORDER BY-Nummer 1 gekennzeichnet ist, enthält eine Variable als Teil des Ausdrucks, die eine Spaltenposition identifiziert. Variablen sind nur zulässig, wenn die Reihenfolge durch einen Ausdruck festgelegt wird, der auf einen Spaltennamen verweist.

Haben Sie einen Vorschlag, wie man das sicher machen kann?

Answer 1

Leider können Sie CFQUERYPARAM nicht direkt in der Order By-Klausel verwenden.

Wenn Sie die Sort By-Variable dynamisch, aber dennoch sicher verwenden möchten, können Sie eine CFSWITCH- oder ähnliche Struktur einrichten, um Ihre SortBy-Variable in Abhängigkeit von einer Bedingung (z. B. einer URL-Variable) zu ändern. Wie immer sollten Sie keine Werte direkt vom Benutzer übergeben, sondern nur die Eingaben des Benutzers betrachten und auf dieser Grundlage aus einer vorgegebenen Liste möglicher Werte auswählen. Verwenden Sie dann einfach die Standardsyntax:

ORDER BY #SortBy#

Answer 2

Ich werde die Antwort von Aaron noch erweitern. Eines der Dinge, die ich tue, ist die Verwendung von listfindnocase(), um sicherzustellen, dass die an die order by-Klausel übergebenen Argumente gültig sind:

<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 3

Das Problem mit der Verwendung des Ordinalwerts für einen Spaltenverweis ist es (ich glaube) der Ordinalwert zum Zeitpunkt der SQL-Anweisung "Tabelle erstellen" wurde ausgeführt - so wie Sie Spalten zur Datenbanktabelle im Laufe der Zeit hinzufügen, kann das GUI-Tool, das Sie verwenden, um die Spalten anzuzeigen, nicht seinen tatsächlichen Ordinalwert darstellen. ich würde wirklich bleiben weg von mit cfqueryparam für diese.

Ich mag die Idee, eine Zahl in den Anfragevariablen (url, Form) zu verwenden, um anzugeben, welche Spalte zu sortieren, und dann verwenden Sie, dass in den Schalter und übersetzen Sie es in einen tatsächlichen Spaltennamen - so dass Sie nicht Ihre Spaltennamen an den Benutzer aussetzen.

Was die Frage angeht, wann/warum man cfqueryparam verwenden sollte, so sollte man bedenken, dass es NICHT nur um die Eingabevalidierung und das Verhindern von SQL-Injektionen geht (obwohl das ein sehr netter Bonus ist) - mit cfqueryparam wird die zugrunde liegende SQL-Anweisung an die Datenbank über den Treiber zurückgesendet, wobei SQL-Bind-Variablen verwendet werden - Platzhalterwerte, so dass der Datenbank-Optimierer bestimmen kann, welcher Index in einem allgemeineren Format verwendet werden soll... wenn Sie also eine SQL-Anweisung wie diese senden: SELECT * FROM product WHERE ID=1 und SELECT * FROM product WHERE ID=2 läuft der Optimierer beide Male. Aber mit Bindungsvariablen sieht die SQL-Anweisung so aus: SELECT * FROM product WHERE ID=? (?=1) und SELECT * FROM produkt WHERE ID=? (?=2), so dass der Optimierer die zwischengespeicherten Ergebnisse der ersten Analyse verwenden kann, um genau zu wissen, welcher Index bei der zweiten Abfrage zu verwenden ist. Je nach Komplexität der SQL und der Datenbank kann dies eine enorme Zeitersparnis bedeuten. meiner Erfahrung nach ist dies bei Oracle und Datums-/Zeitspalten in der Where-Klausel leistungsmäßig sehr hilfreich.

so weit, wo cfqueryparam zu verwenden, seine, wo eine SQL-Bind-Variable verwendet werden kann ...

hth jon

Answer 4

In Bezug auf den Kommentar über die Verwendung von "cfqueryparam in der Auftragsklausel gut mit MySQL". Ja, ich glaube, es ist mit MySQL-Datenquellen erlaubt. Obwohl die Verwendung der Spalte Ordinal nicht den Spaltennamen (der stattdessen als konstante Zeichenkette behandelt zu werden scheint).

Leider scheint es bei MS SQL-Datenquellen überhaupt nicht zu funktionieren. Zumindest nicht, soweit ich das beurteilen kann.

<!--- 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#">

更新しています： Zu den Bemerkungen über die Ordnungszahl: Nein, ich glaube, es bezieht sich auf die Spaltenposition in der Auswahlliste, nicht auf die zugrunde liegende Tabelle. Es sollte also in Ordnung sein.

Ja, ich stimme zu, dass der Schutz vor Sql-Injection nicht der Hauptzweck von cfqueryparam ist. Daher war die Beschreibung der Bindungsvariablen eine gute Ergänzung.

Answer 5

Ich dachte, ich würde dieses Problem mit weniger Code angehen:

<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>

Wo sortBy und sortDirection kommen über die URL oder wo auch immer herein.

Ich mag das, weil es sauber ist und man nichts über die ORDER BY-Klausel einfügen kann.

Irgendwelche Kommentare?