У меня есть переменная, которая представляет собой массив строк. Я хочу передать все значения переменной, объединяя все ее элементы в одну строку.
Но я не уверен, что это создает риск внедрения SQL. Мой код:
private string concatenateStrings(string[] sa)
{
StringBuilder sb = new StringBuilder();
foreach (string s in sa)
{
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append("'");
sb.Append(s);
sb.Append("'");
}
return sb.ToString();
}
public void UpdateClaimSts(string[] ids)
{
string query = @"UPDATE MYTABLE
SET STATUS = 'X'
WHERE TABLEID in (" + concatenateStrings(ids) + ")";
OracleCommand dbCommand = (OracleCommand)this.Database.GetSqlStringCommand(query) as OracleCommand;
this.Database.ExecuteNonQuery(dbCommand, this.Transaction);
}
Я попытался изменить запрос для использования параметризованных запросов:
string query = @"UPDATE MYTABLE
SET STATUS = 'X'
WHERE TABLEID in (:ids)";
OracleCommand dbCommand = (OracleCommand)this.Database.GetSqlStringCommand(query) as OracleCommand;
dbCommand.Parameters.Add(":ids", OracleType.VarChar).Value = concatenateStrings(ids);
this.Database.ExecuteNonQuery(dbCommand, this.Transaction);
Но это не работает. Есть идеи?
В качестве быстрого и частичного (мы предполагаем, TABLEID
поле TABLEID
имеет тип NUMBER
), вы можете проверить, что каждый элемент в sa
является действительным целым числом:
private string concatenateStrings(string[] sa) {
return string.Join(", ", sa
.Where(item => Regex.IsMatch(item, @"^\-?[0-9]+$")));
}
public void UpdateClaimSts(string[] ids) {
string query = string.Format(
@"UPDATE MYTABLE
SET STATUS = 'X'
WHERE TABLEID IN ({0})", concatenateStrings(ids));
...
В общем случае вы можете попробовать использовать переменные связывания (пожалуйста, обратите внимание на множественное число: мы должны создать многие из них):
public void UpdateClaimSts(string[] ids) {
// :id_0, :id_1, ..., :id_N
string bindVariables = string.Join(", ", ids
.Select((id, index) => ":id_" + index.ToString()));
string query = string.Format(
@"UPDATE MYTABLE
SET STATUS = 'X'
WHERE TABLEID IN ({0})", bindVariables);
// Do not forget to wrap IDisposable into "using"
using (OracleCommand dbCommand = ...) {
...
// Each item of the ids should be assigned to its bind variable
for (int i = 0; i < ids.Length; ++i)
dbCommand.Parameters.Add(":id_" + i.ToString(), OracleType.VarChar).Value = ids[i];
...
int[]
вместо string[]
и избежать регулярного выражения. Ваше второе предложение выглядит хорошо для меня, хотя.
Number
и .Net int
( long
) могут быть совершенно разных типов (например, Number(20)
превышает long
), поэтому int[]
вместо string[]
является опасным решением. Однако мы часто используем Number(N)
в качестве ключа, и в этом случае мое первое частичное решение будет работать
Создайте процедуру PL/SQL (внутри пакета PL/SQL) следующим образом:
TYPE TArrayOfVarchar2 IS TABLE OF MYTABLE.TABLEID%TYPE INDEX BY PLS_INTEGER;
PROCEDURE UPDATE_MYTABLE(TABLEIDs IN TArrayOfVarchar2) IS
BEGIN
FORALL i IN INDICES OF TABLEIDs
UPDATE MYTABLE SET STATUS = 'X' WHERE TABLEID = TABLEIDs(i);
END;
и позвоните так:
using (OracleCommand cmd = new OracleCommand("BEGIN UPDATE_MYTABLE(:tableId); END;"), con))
{
cmd.CommandType = CommandType.Text;
// or
// OracleCommand cmd = new OracleCommand("UPDATE_MYTABLE"), con);
// cmd.CommandType = CommandType.StoredProcedure;
var par = cmd.Parameters.Add("tableId", OracleDbType.Varchar2, ParameterDirection.Input);
par.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
par.Value = sa;
par.Size = sa.Length;
cmd.ExecuteNonQuery();
}
С# имеет тип OracleCollectionType.PLSQLAssociativeArray
для передачи массивов в тип данных ассоциативного массива PL/SQL, но это не может использоваться в SQL-запросах, поскольку это только структура данных PL/SQL.
К сожалению, он не поддерживает передачу массива в тип данных SQL Collection (который может использоваться в SQL-запросе).
Одна из них - попросить своего администратора базы данных создать простую функцию для преобразования ассоциативного массива PL/SQL в коллекцию SQL, а затем использовать это как промежуточный шаг в запросе:
CREATE TYPE varchar2s_array_type IS TABLE OF VARCHAR2(100)
/
CREATE PACKAGE utils IS
TYPE varchar2s_assoc_array_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
FUNCTION assoc_array_to_collection(
p_assoc_array IN varchar2s_assoc_array_type
) RETURN varchar2s_array_type DETERMINISTIC;
END;
/
CREATE PACKAGE BODY utils IS
FUNCTION assoc_array_to_collection(
p_assoc_array IN varchar2s_assoc_array_type
) RETURN varchar2s_array_type DETERMINISTIC
IS
p_array varchar2s_array_type := varchar2s_array_type();
i PLS_INTEGER;
BEGIN
IF p_assoc_array IS NOT NULL THEN
i := p_assoc_array.FIRST;
LOOP
EXIT WHEN i IS NULL;
p_array.EXTEND();
p_array(p_array.COUNT) := p_assoc_array(i);
i := p_assoc_array.NEXT(i);
END LOOP;
END IF;
RETURN p_array;
END;
END;
/
Затем вы можете изменить свой код, чтобы использовать MEMBER OF
а не IN
в выражении SQL:
UPDATE MYTABLE
SET STATUS = 'X'
WHERE TABLEID MEMBER OF utils.assoc_array_to_collection(:ids)
И привяжите параметр, используя что-то вроде (я не являюсь пользователем С#, поэтому это просто для того, чтобы дать вам общее представление о методе, даже если синтаксис не совсем корректен):
var par = cmd.Parameters.Add(":ids", OracleDbType.Varchar2, ParameterDirection.Input);
par.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
par.Value = ids;
par.Size = ids.Length;
cmd.ExecuteQuery();
Затем вы можете повторно использовать общую функцию для многих запросов.
id
1');DROP TABLE MYTABLE;--
(например), тогда у вас будут некоторые проблемы