Pytanie Sparametryzowane zapytania o warunkach LIKE i IN


Sparametryzowane zapytania w .Net zawsze wyglądają tak jak w przykładach:

SqlCommand comm = new SqlCommand(@"
   SELECT * 
   FROM   Products 
   WHERE  Category_ID = @categoryid
", 
   conn);
comm.Parameters.Add("@categoryid", SqlDbType.Int);
comm.Parameters["@categoryid"].Value = CategoryID;

Ale wpadam na ceglaną ścianę próbując wykonać następujące czynności:

SqlCommand comm = new SqlCommand(@"
   SELECT * 
   FROM   Products 
   WHERE  Category_ID IN (@categoryids) 
      OR  name LIKE '%@name%'
", 
   conn);
comm.Parameters.Add("@categoryids", SqlDbType.Int);
comm.Parameters["@categoryids"].Value = CategoryIDs;
comm.Parameters.Add("@name", SqlDbType.Int);
comm.Parameters["@name"].Value = Name;

Gdzie

  • CategoryID jest rozdzielaną przecinkami listą liczb "123,456,789" (bez cudzysłowów)
  • Nazwa to ciąg znaków, prawdopodobnie z pojedynczymi cudzysłowami i innymi złymi znakami

Jaka jest prawidłowa składnia tego?


46
2017-11-19 19:56


pochodzenie


W klamrze "IN" musisz podać każdą wartość w poleceniu SQL. I wartość evenry dodaj do kolekcji "Parameters". Jeśli przekazujesz wartość ciągu znaków do SQL przez wartość parametru, nie musisz obawiać się wtrysku sql. - TcKs
Możesz napisać zapytanie jak WHERE name LIKE CONCAT('%', ?, '%') - Salman A


Odpowiedzi:


Załóżmy, że masz identyfikatory kategorii w postaci liczb całkowitych, a Name to ciąg znaków. Sztuką jest stworzenie tekstu polecenia, aby umożliwić wprowadzanie wszystkich identyfikatorów kategorii jako indywidualnych parametrów i skonstruowanie dopasowania rozmytego dla nazwy. Aby to zrobić, używamy pętli do skonstruowania sekwencji nazw parametrów od p0 do @ pN-1, gdzie N jest liczbą identyfikatorów kategorii w tablicy. Następnie konstruujemy parametr i dodajemy go do polecenia z powiązanym identyfikatorem kategorii jako wartością dla każdego nazwanego parametru. Następnie używamy konkatenacji do nazwy w samym zapytaniu, aby umożliwić wyszukiwanie fuzzy na nazwę.

string Name = "someone";
int[] categoryIDs = new int[] { 238, 1138, 1615, 1616, 1617,
                                1618, 1619, 1620, 1951, 1952,
                                1953, 1954, 1955, 1972, 2022 };

SqlCommand comm = conn.CreateCommand();

string[] parameters = new string[categoryIDs.Length];
for(int i=0;i<categoryIDs.Length;i++)
{
   parameters[i] = "@p"+i;
   comm.Parameters.AddWithValue(parameters[i], categoryIDs[i]);
}
comm.Parameters.AddWithValue("@name",$"%{Name}%");
comm.CommandText = "SELECT * FROM Products WHERE Category_ID IN (";
comm.CommandText += string.Join(",", parameters) + ")";
comm.CommandText += " OR name LIKE @name";

Jest to w pełni sparametryzowana kwerenda, która powinna zadowolić DBA. Podejrzewam, że skoro są to liczby całkowite, to nie byłoby większego ryzyka związanego z bezpieczeństwem - wystarczy skonstruować tekst polecenia bezpośrednio z wartościami, a jednocześnie sparametryzować nazwę. Jeśli identyfikatory kategorii są w tablicy łańcuchowej, wystarczy podzielić tablicę na przecinki, przekonwertować je na liczbę całkowitą i zapisać w tablicy liczb całkowitych.

Uwaga: Mówię tablicę i używam jej w przykładzie, ale powinno działać dla każdej kolekcji, chociaż twoja iteracja prawdopodobnie będzie się różnić.

Oryginalny pomysł od http://www.tek-tips.com/viewthread.cfm?qid=1502614&page=9


57
2017-11-19 21:26



Twój DBA będzie szalony, jeśli mocno skasujesz parametry w SQL, nawet jeśli są one ints, ponieważ oznacza to, że DB nie może użyć pamięci podręcznej instrukcji i musi ponownie przeanalizować instrukcję za każdym razem. - Mr. Shiny and New 安宇
Twoje rozwiązanie jest nadal otwarte na wtrysk sql. na przykład jeśli nazwa zawiera:% "; drop table Orders; - - ttomsen
@ttomsen dzięki, nie wiem, jak to przegapiłem. Naprawiono teraz - tvanfosson


Potrzebujesz "%" w wartości parametru sql.

SqlCommand comm = new SqlCommand("SELECT * FROM Products WHERE Category_ID IN (@categoryid1, @categoryid2) OR name LIKE @name", conn);
comm.Parameters.Add("@categoryid1", SqlDbType.Int);
comm.Parameters["@categoryid1"].Value = CategoryID[0];
comm.Parameters.Add("@categoryid2", SqlDbType.Int);
comm.Parameters["@categoryid2"].Value = CategoryID[1];
comm.Parameters.Add("@name", SqlDbType.Int);
comm.Parameters["@name"].Value = "%" + Name + "%";

11
2017-11-19 20:05





To podejście nie zadziała. Kropka.

Klauzula IN oczekuje samej listy parametrów, więc po związaniu jeden do tego parametru, masz szansę się przekazać jeden wartość.

Twórz ciąg wyciągów dynamicznie, z dokładną ilością poszczególnych elementów zastępczych klauzuli IN, które zamierzasz przekazać, a następnie dodaj parametry i powiązaj je z nimi w pętli.


6
2017-11-19 20:43



Inną sztuczką jest tworzenie wielu parametrów w SQL i wielokrotne wiązanie wartości. Jest to nieeleganckie, ale działa, ponieważ IN (1,2,3) i IN (1,2,3, 1,2,3, 1,2) są równoważnymi predykatami. - Bill Karwin
Tak, nie ładnie, ale skutecznie. Serwer uporządkuje duplikaty. - Tomalak


nie jestem pewien, czy to jest właściwa droga, ale jest to sposób, w jaki zrobiłem to w Przed

lista templist = nowa lista

comm.Parameters.Add ("@ categoryids", SqlDbType.varchar); comm.Parameters ["@ categoryids"]. value = string.join (",", templist.toarray ())


-2
2017-11-19 20:37



Nie. Nie można sparametryzować listy wartości. Cóż, możesz, ale porówna Category_ID z singlem strunowy wartość "1,2,3", zamiast porównywania z każdą wartością całkowitą na liście. Będzie to miało niezamierzone skutki. :-) - Bill Karwin