Pertanyaan Tentukan parameter klausa SQL IN


Bagaimana cara membuat parameter kueri yang berisi IN klausa dengan sejumlah variabel argumen, seperti ini?

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

Dalam kueri ini, jumlah argumen bisa berada di mana saja dari 1 hingga 5.

Saya lebih suka untuk tidak menggunakan prosedur tersimpan khusus untuk ini (atau XML), tetapi jika ada beberapa cara elegan khusus untuk SQL Server 2008Saya terbuka untuk itu.


950
2017-12-03 16:16


asal


Jawaban:


Inilah teknik cepat dan kotor yang saya gunakan:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

Jadi, inilah kode C #:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

Dua peringatan:

  • Pertunjukannya mengerikan. LIKE "%...%" kueri tidak diindeks.
  • Pastikan Anda tidak memilikinya |, kosong, atau tag kosong atau ini tidak akan berfungsi

Ada cara lain untuk mencapai hal ini yang beberapa orang mungkin menganggapnya lebih bersih, jadi teruslah membaca.


288
2017-12-03 16:41



Anda dapat menentukan parameter setiap nilai, jadi sesuatu seperti:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

Yang akan memberi Anda:

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"

Tidak, ini tidak terbuka untuk Injeksi SQL. Satu-satunya teks yang disuntikkan ke CommandText tidak didasarkan pada input pengguna. Ini hanya didasarkan pada awalan "@tag" yang dikodekan keras, dan indeks larik. Indeks akan selalu menjadi integer, bukan buatan pengguna, dan aman.

Nilai yang dimasukkan pengguna masih dimasukkan ke dalam parameter, sehingga tidak ada kerentanan di sana.

Edit:

Terlepas dari masalah injeksi, berhati-hatilah untuk mencatat bahwa membangun teks perintah untuk mengakomodasi sejumlah variabel parameter (seperti di atas) menghalangi kemampuan SQL server untuk mengambil keuntungan dari query cache. Hasil bersihnya adalah bahwa Anda hampir pasti kehilangan nilai menggunakan parameter di tempat pertama (sebagai lawan hanya memasukkan string predikat ke dalam SQL itu sendiri).

Bukannya rencana kueri cache tidak berharga, tetapi IMO kueri ini tidak cukup rumit untuk melihat banyak manfaat darinya. Meskipun biaya kompilasi mungkin mendekati (atau bahkan melebihi) biaya pelaksanaan, Anda masih berbicara dalam milidetik.

Jika Anda memiliki cukup RAM, saya berharap SQL Server mungkin akan cache rencana untuk jumlah parameter umum juga. Saya kira Anda selalu dapat menambahkan lima parameter, dan membiarkan tag yang tidak ditentukan menjadi NULL - rencana permintaan harus sama, tetapi tampaknya cukup jelek bagi saya dan saya tidak yakin bahwa itu akan bernilai mikro-optimasi (meskipun, pada Stack Overflow - mungkin sangat berharga).

Juga, SQL Server 7 dan kemudian akan kueri otomatis parameterJadi, menggunakan parameter tidak benar-benar diperlukan dari sudut pandang kinerja - itu, bagaimanapun, kritis dari sudut pandang keamanan - terutama dengan data yang dimasukkan pengguna seperti ini.


675
2017-12-03 16:35



Untuk SQL Server 2008, Anda bisa menggunakan parameter meja dihargai. Ini sedikit kerja, tetapi bisa dibilang lebih bersih dari metode saya yang lain.

Pertama, Anda harus membuat tipe

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

Kemudian, kode ADO.NET Anda terlihat seperti ini:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";

// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";

// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
    if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows
    var firstRecord = values.First();
    var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}

235
2017-12-03 16:53



Pertanyaan aslinya adalah "Bagaimana saya membuat parameter permintaan ..."

Biarkan saya menyatakan di sini, bahwa ini bukan jawaban ke pertanyaan aslinya. Sudah ada beberapa demonstrasi yang dalam jawaban baik lainnya.

Dengan mengatakan, lanjutkan dan panjikan jawaban ini, turunkan suara, tandai sebagai bukan jawaban ... lakukan apa pun yang Anda yakini benar.

Lihat jawaban dari Mark Brackett untuk jawaban yang lebih disukai yang saya (dan 231 lainnya) upvoted. Pendekatan yang diberikan dalam jawabannya memungkinkan 1) untuk penggunaan variabel pengikat yang efektif, dan 2) untuk predikat yang dapat dibuktikan.

Jawaban yang dipilih

Yang ingin saya sampaikan di sini adalah pendekatan yang diberikan dalam jawaban Joel Spolsky, jawabannya "dipilih" sebagai jawaban yang benar.

Pendekatan Joel Spolsky sangat pintar. Dan itu bekerja secara wajar, itu akan menunjukkan perilaku yang dapat diprediksi dan kinerja yang dapat diprediksi, mengingat nilai "normal", dan dengan kasus tepi normatif, seperti NULL dan string kosong. Dan itu mungkin cukup untuk aplikasi tertentu.

Tetapi dalam hal generalisasi pendekatan ini, mari kita juga mempertimbangkan kasus sudut yang lebih tidak jelas, seperti ketika Name kolom berisi karakter wildcard (seperti yang diakui oleh predikat SEPERTI.) Karakter wildcard yang saya lihat paling sering digunakan adalah % (tanda persen.). Jadi mari kita berurusan dengan itu di sini sekarang, dan kemudian melanjutkan ke kasus lain.

Beberapa masalah dengan karakter%

Pertimbangkan nilai Nama dari 'pe%ter'. (Untuk contoh di sini, saya menggunakan nilai string literal di tempat nama kolom.) Baris dengan nilai Nama `'pe% ter' akan dikembalikan oleh query dari bentuk:

select ...
 where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'

Tapi itu baris yang sama tidak dikembalikan jika urutan istilah pencarian dibalik:

select ...
 where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'

Perilaku yang kita amati agak aneh. Mengubah urutan istilah pencarian dalam daftar mengubah hasil yang ditetapkan.

Hampir tidak perlu dikatakan bahwa kita mungkin tidak menginginkannya pe%ter untuk mencocokkan selai kacang, tidak peduli betapa dia menyukainya.

Kasus sudut tidak jelas

(Ya, saya akan setuju bahwa ini adalah kasus yang tidak jelas. Mungkin salah satu yang tidak mungkin diuji. Kami tidak akan mengharapkan wildcard dalam nilai kolom. Kami dapat berasumsi bahwa aplikasi mencegah nilai tersebut dari disimpan. Tapi dalam pengalaman saya, saya jarang melihat kendala basis data yang secara khusus melarang karakter atau pola yang dianggap sebagai wildcard di sisi kanan LIKE operator perbandingan.

Menambal lubang

Salah satu pendekatan untuk menambal lubang ini adalah untuk melarikan diri % karakter wildcard. (Bagi siapa pun yang tidak akrab dengan klausa pelarian pada operator, inilah tautan ke Dokumentasi SQL Server.

select ...
 where '|peanut|butter|'
  like '%|' + 'pe\%ter' + '|%' escape '\'

Sekarang kita dapat mencocokkan% harfiah. Tentu saja, ketika kita memiliki nama kolom, kita harus secara dinamis melarikan diri dari wildcard. Kami bisa menggunakan REPLACE berfungsi untuk menemukan kemunculan %karakter dan masukkan karakter backslash di depan masing-masing karakter, seperti ini:

select ...
 where '|pe%ter|'
  like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape '\'

Sehingga memecahkan masalah dengan% wildcard. Hampir.

Escape the escape

Kami menyadari bahwa solusi kami telah memperkenalkan masalah lain. Karakter melarikan diri. Kami melihat bahwa kami juga akan perlu untuk melarikan diri dari setiap kejadian karakter pelarian itu sendiri. Kali ini, kami menggunakan! sebagai karakter pelarian:

select ...
 where '|pe%t!r|'
  like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'

Garis bawahnya juga

Sekarang kita sudah siap, kita dapat menambahkan yang lain REPLACE menangani wildcard garis bawah. Dan hanya untuk bersenang-senang, kali ini, kita akan menggunakan $ sebagai karakter pelarian.

select ...
 where '|p_%t!r|'
  like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

Saya lebih memilih pendekatan ini untuk melarikan diri karena bekerja di Oracle dan MySQL serta SQL Server. (Saya biasanya menggunakan \ backslash sebagai karakter pelarian, karena itu karakter yang kita gunakan dalam ekspresi reguler. Tapi mengapa dibatasi oleh konvensi!

Brengsek sial itu

SQL Server juga memungkinkan karakter wildcard diperlakukan sebagai literal dengan melampirkannya dalam tanda kurung []. Jadi kami belum selesai memperbaikinya, setidaknya untuk SQL Server. Karena sepasang tanda kurung memiliki arti khusus, kita juga harus menghindarinya. Jika kita berhasil meloloskan diri dengan benar, setidaknya kita tidak perlu repot dengan tanda hubung - dan karat ^ dalam kurung. Dan kita bisa meninggalkannya %dan _ karakter di dalam kurung melarikan diri, karena kita pada dasarnya akan menonaktifkan arti khusus dari tanda kurung.

Menemukan pasangan kurung yang cocok seharusnya tidak sesulit itu. Ini sedikit lebih sulit daripada menangani kejadian tunggal% dan _. (Perhatikan bahwa itu tidak cukup untuk melarikan diri dari semua kejadian tanda kurung, karena braket tunggal dianggap sebagai literal, dan tidak perlu melarikan diri. Logika semakin sedikit lebih buram daripada yang dapat saya tangani tanpa menjalankan lebih banyak kasus uji .)

Ekspresi sebaris menjadi berantakan

Ekspresi sebaris dalam SQL semakin panjang dan jelek. Kita mungkin bisa membuatnya berhasil, tetapi surga membantu jiwa miskin yang datang dan harus menguraikannya. Seperti kebanyakan penggemar saya untuk ekspresi inline, saya cenderung tidak menggunakan satu di sini, terutama karena saya tidak ingin meninggalkan komentar yang menjelaskan alasan kekacauan, dan meminta maaf untuk itu.

Sebuah fungsi di mana?

Oke, jadi, jika kita tidak menangani itu sebagai ekspresi sebaris dalam SQL, alternatif terdekat yang kita miliki adalah fungsi yang ditentukan pengguna. Dan kita tahu bahwa tidak akan mempercepat apa pun (kecuali kita dapat mendefinisikan indeks di atasnya, seperti yang kita bisa dengan Oracle.) Jika kita harus membuat fungsi, kita mungkin lebih baik melakukan itu dalam kode yang memanggil SQL pernyataan.

Dan fungsi itu mungkin memiliki beberapa perbedaan dalam perilaku, tergantung pada DBMS dan versinya. (Suatu teriakan untuk semua pengembang Java Anda sangat tertarik untuk dapat menggunakan mesin basis data secara bergantian.)

Pengetahuan domain

Kami mungkin memiliki pengetahuan khusus tentang domain untuk kolom, (yaitu, himpunan nilai yang diizinkan diterapkan untuk kolom. Kami mungkin tahu a priori bahwa nilai-nilai yang disimpan dalam kolom tidak akan pernah mengandung tanda persen, garis bawah, atau pasangan braket. Dalam hal ini, kami hanya menyertakan komentar singkat bahwa kasus-kasus tersebut tertutup.

Nilai-nilai yang disimpan dalam kolom memungkinkan untuk% atau _ karakter, tetapi kendala mungkin memerlukan nilai-nilai itu untuk melarikan diri, mungkin menggunakan karakter yang ditentukan, sehingga nilai-nilai adalah SEPERTI perbandingan "aman". Sekali lagi, komentar singkat tentang set nilai yang diizinkan, dan khususnya karakter mana yang digunakan sebagai karakter pelarian, dan ikuti pendekatan Joel Spolsky.

Namun, tanpa pengetahuan khusus dan jaminan, penting bagi kami untuk setidaknya mempertimbangkan penanganan kasus-kasus sudut yang tidak jelas, dan mempertimbangkan apakah perilaku tersebut masuk akal dan "sesuai spesifikasi".


Isu-isu lain direkapitulasi

Saya percaya orang lain sudah cukup menunjukkan beberapa bidang yang dianggap penting lainnya:

  • Injeksi SQL (Mengambil apa yang tampaknya menjadi informasi yang diberikan pengguna, dan termasuk yang dalam teks SQL daripada memasok mereka melalui variabel terikat. Menggunakan variabel bind tidak diperlukan, itu hanya satu pendekatan yang mudah untuk menggagalkan dengan injeksi SQL. Ada cara lain untuk menghadapinya:

  • rencana optimizer menggunakan indeks scan daripada mencari indeks, mungkin perlu untuk ekspresi atau fungsi untuk melarikan diri wildcard (kemungkinan indeks pada ekspresi atau fungsi)

  • menggunakan nilai literal di tempat variabel mengikat berdampak skalabilitas


Kesimpulan

Saya suka pendekatan Joel Spolsky. Itu pintar. Dan itu berhasil.

Tapi begitu saya melihatnya, saya segera melihat masalah potensial dengan itu, dan bukan sifat saya untuk membiarkannya. Saya tidak bermaksud bersikap kritis terhadap upaya orang lain. Saya tahu banyak pengembang yang menganggap pekerjaan mereka sangat pribadi, karena mereka begitu banyak berinvestasi ke dalamnya dan mereka sangat peduli tentang hal itu. Jadi tolong mengerti, ini bukan serangan pribadi. Yang saya identifikasi di sini adalah jenis masalah yang muncul dalam produksi daripada pengujian.

Ya, saya sudah jauh dari pertanyaan aslinya. Tetapi di mana lagi meninggalkan catatan ini mengenai apa yang saya anggap sebagai masalah penting dengan jawaban "terpilih" untuk sebuah pertanyaan?


176
2018-05-29 23:18



Anda dapat meneruskan parameter sebagai string

Jadi, Anda memiliki string

DECLARE @tags

SET @tags = ‘ruby|rails|scruffy|rubyonrails’

select * from Tags 
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc

Maka yang harus Anda lakukan hanyalah meneruskan string sebagai 1 parameter.

Berikut adalah fungsi split yang saya gunakan.

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

123
2017-12-03 16:27



Saya mendengar Jeff / Joel membicarakan hal ini di podcast hari ini (episode 34, 2008-12-16 (MP3, 31 MB), 1 jam 03 menit 38 detik - 1 jam 06 menit 45 detik), dan saya pikir saya ingat Stack Overflow sedang menggunakan LINQ to SQL, tapi mungkin itu dibuang. Inilah hal yang sama di LINQ to SQL.

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
              where inValues.Contains(tag.Name)
              select tag;

Itu dia. Dan, ya, LINQ sudah terlihat cukup ke belakang, tetapi Contains klausul tampak ekstra mundur bagi saya. Ketika saya harus melakukan permintaan yang sama untuk sebuah proyek di tempat kerja, saya secara alami mencoba melakukan ini dengan cara yang salah dengan melakukan penggabungan antara array lokal dan tabel SQL Server, mencari LINQ to SQL translator akan cukup pintar untuk menangani terjemahan entah bagaimana. Tidak, tapi itu memberikan pesan kesalahan yang deskriptif dan menunjuk saya untuk menggunakan Mengandung.

Lagi pula, jika Anda menjalankan ini sangat dianjurkan LINQPad, dan menjalankan kueri ini, Anda dapat melihat SQL aktual yang dibuat penyedia LINQ SQL. Ini akan menunjukkan kepada Anda masing-masing nilai menjadi parameter IN ayat.


63
2017-12-19 05:40



Jika Anda menelepon dari .NET, Anda bisa menggunakannya Dapper dot net:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = dataContext.Query<Tags>(@"
select * from Tags 
where Name in @names
order by Count desc", new {names});

Di sini Dapper melakukan pemikiran, jadi Anda tidak perlu melakukannya. Sesuatu yang serupa dimungkinkan dengan LINQ to SQL, tentu saja:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = from tag in dataContext.Tags
           where names.Contains(tag.Name)
           orderby tag.Count descending
           select tag;

43
2018-06-15 11:04