Как вы используете cfqueryparam в предложении ORDER BY?

Я пытаюсь быть хорошим веб-разработчиком CF и использую <cfqueryparam> вокруг всех элементов FORM или URL, которые <cfqueryparam> в мои SQL-запросы.

В этом случае я пытаюсь разрешить пользователю управлять предложением 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> 

Когда я это сделаю, я получаю следующую ошибку:

Элемент SELECT, идентифицированный ORDER BY номером 1, содержит переменную как часть выражения, идентифицирующего позицию столбца. Переменные допускаются только при заказе с помощью выражения, ссылающегося на имя столбца.

Любые предложения о том, как сделать это безопасно?

К сожалению, вы не можете использовать CFQUERYPARAM непосредственно в предложении Order By.

Если вы хотите динамически использовать Dynamic Order, но все же можете сделать это безопасно, вы можете настроить CFSWITCH или аналогичную структуру для изменения переменной SortBy в зависимости от некоторого состояния (скажем, переменной URL). Как всегда, не передавайте никакие значения непосредственно от пользователя, просто посмотрите на ввод пользователя и выберите из предопределенного списка возможных значений на основе этого. Затем просто используйте стандартный синтаксис:

 ORDER BY #SortBy# 

Я просто расширю ответ Аарона. Одна из вещей, которые я делаю, – это использовать listfindnocase (), чтобы убедиться, что аргументы, переданные в предложении order by, действительны:

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

проблема с использованием порядкового значения для ссылки на столбец заключается в том, что это (я считаю) порядковое значение на момент выполнения оператора SQL таблицы create – так, как вы добавляете столбцы в таблицу базы данных с течением времени, инструмент GUI, который вы используете для отображение столбцов может не соответствовать фактическому порядковому значению. я бы действительно держался подальше от использования cfqueryparam для этого.

Мне нравится идея использования числа в переменных запроса (url, form), чтобы указать, какой столбец будет сортироваться, а затем использовать его в коммутаторе и перевести его в фактическое имя столбца – чтобы вы не отображали имена столбцов в пользователь.

насколько когда / зачем использовать cfqueryparam, имейте в виду его НЕ только подтверждение ввода и предотвращение SQL-инъекций (хотя это очень хороший бонус) – с cfqueryparam базовый SQL в базу данных отправляется обратно через драйвер, используя SQL bind переменные – значения-заполнители, поэтому оптимизатор базы данных может определить, какой индекс использовать в более общем формате … поэтому, когда вы отправляете инструкцию SQL следующим образом: SELECT * FROM product WHERE ID = 1 и SELECT * FROM product WHERE ID = 2 оптимизатор работает одновременно. но с переменными связывания, SQL выглядит так: SELECT * FROM product WHERE ID =? (? = 1) и SELECT * FROM product WHERE ID =? (? = 2), поэтому оптимизатор может использовать кэшированные результаты первого анализа, чтобы точно знать, какой индекс использовать для второго запроса. в зависимости от сложности SQL и базы данных это может быть ОГРОМНАЯ экономия во времени. по моему опыту, его очень полезную работу с графами оракула и даты / времени в предложении where.

насколько можно использовать cfqueryparam, где можно использовать переменную связывания SQL …

hth jon

Что касается комментария об использовании «cfqueryparam в порядке заказа с MySQL». Да, я считаю, что это разрешено с базами данных MySQL. Хотя использовать порядковый номер столбца, а не имя столбца (которое, как представляется, рассматривается как постоянная строка).

К сожалению, он вообще не работает для баз данных MS SQL. По крайней мере, не из того, что я могу сказать.

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

Обновление: Что касается комментариев об ординале: Нет, я считаю, что это относится к позиции столбца в списке выбора, а не в базовой таблице. Так что все должно быть хорошо.

Да, я согласен с тем, что защита sql-инъекций не является основной целью cfqueryparam. Таким образом, описание переменных связывания было хорошим дополнением.

Думал, что я бы бросил меньше кода на эту проблему:

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

Где sortBy и sortDirection входят через URL-адрес или где-либо еще.

Мне это нравится, потому что оно чистое, и вы не можете ничего вводить с помощью предложения ORDER BY.

Любые комментарии?