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

Question

Я пытаюсь быть хорошим веб-разработчиком CF и использую <cfqueryparam> вокруг всех элементов FORM или URL, которые попадают в мои 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, идентифицируемый номером 1 ORDER BY, содержит переменную как часть выражения, определяющую позицию столбца. Переменные допускаются только при упорядочении по выражению, ссылающемуся на имя столбца.

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

Answer 1

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

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

ORDER BY #SortBy#

Answer 2

Я просто расширю ответ Аарона. Одна из вещей, которые я делаю, - это использование 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>

Answer 3

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

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

Что касается того, когда / зачем использовать cfqueryparam, имейте в виду, что он НЕ только для проверки ввода и предотвращения внедрения SQL (хотя это очень приятный бонус) - с помощью cfqueryparam базовый SQL в базу данных отправляется обратно через драйвер с помощью привязки SQL переменные - значения заполнителей, поэтому оптимизатор базы данных может определить, какой индекс использовать в более общем формате... поэтому, когда вы отправляете SQL-оператор, например, такой: SELECT * FROM product WHERE ID=1 и SELECT * FROM product WHERE ID=2 оптимизатор работает оба раза. но с переменными связывания SQL выглядит так: SELECT * FROM product WHERE ID=? (?=1) и ВЫБРАТЬ * ИЗ ПРОДУКТА, ГДЕ ID=? (?=2), поэтому оптимизатор может использовать кэшированные результаты первого анализа, чтобы точно знать, какой индекс использовать во втором запросе. в зависимости от сложности SQL и базы данных это может быть ОГРОМНОЙ экономией времени. По моему опыту, это очень полезно для работы с оракулом и столбцами даты / времени в предложении where.

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

Хтон Джон

Answer 4

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

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

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

Answer 5

Относительно комментария об использовании "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. Таким образом, описание переменных связывания было хорошим дополнением.