cfstoredproc: panggil stored procedure dalam CFML

cfstoredproc ialah tag CFML yang memanggil stored procedure di pangkalan data melalui sambungan JDBC atau ODBC. Ia mengenal pasti prosedur dengan atribut procedure dan datasource, menghantar parameter dengan cfprocparam, lalu menangkap hasil dengan cfprocresult. Artikel ini menerangkan cara ketiga-tiga tag itu bekerja, perbezaan antara set hasil dan nilai tunggal, serta bila stored procedure sebenarnya pilihan yang lebih baik daripada SQL biasa dalam CFML.

Apa itu cfstoredproc

cfstoredproc melaksanakan stored procedure, iaitu rutin yang disimpan dan dijalankan di dalam pangkalan data itu sendiri. Dua atribut paling asas ialah procedure, nama prosedur di pelayan pangkalan data, dan datasource, nama sambungan yang didaftarkan dalam Lucee Administrator (lihat konfigurasi datasource). Atribut procedure ialah satu-satunya atribut yang wajib; username, password, blockFactor, debug, returnCode, result, cachedWithin dan cachedAfter semuanya pilihan.

Stored procedure hidup di dalam pangkalan data, bukan di dalam aplikasi CFML. Logiknya ditulis dalam SQL atau bahasa prosedur pangkalan data, dan aplikasi hanya perlu tahu nama prosedur serta susunan parameternya.

MySQL lawan H2: DELIMITER dan CREATE PROCEDURE

Pangkalan data yang paling biasa dalam aplikasi CFML ialah MySQL, dan sintaksnya berbeza dengan H2. MySQL mencipta stored procedure dengan CREATE PROCEDURE, dan kerana badan prosedur mengandungi koma bertitik, anda mesti menukar pembatas dengan DELIMITER supaya koma bertitik dalam badan tidak dianggap penghujung arahan. Blok ini diambil daripada sintaks rasmi MySQL dan belum dijalankan dalam rig H2, kerana H2 tidak mempunyai CREATE PROCEDURE gaya MySQL:

DELIMITER //

CREATE PROCEDURE kira_jualan(
    IN  id_kategori INT,
    OUT jumlah_jualan DECIMAL(10,2),
    INOUT nota VARCHAR(200)
)
BEGIN
    SELECT SUM(harga * kuantiti) INTO jumlah_jualan
    FROM jualan WHERE kategori_id = id_kategori;

    SELECT id, nama, harga FROM produk
    WHERE kategori_id = id_kategori;

    SET nota = CONCAT(nota, ' siap');
END //

DELIMITER ;

Prosedur ini menunjukkan ketiga-tiga jenis parameter dan satu set hasil. H2 pula menggunakan CREATE ALIAS dengan sumber Java dan bukannya CREATE PROCEDURE. Alias yang menerima Connection sebagai parameter pertama dan memulangkan ResultSet bertindak seperti prosedur yang memulangkan set hasil. Blok sql ini benar-benar dijalankan dalam rig Lucee 7.1 dengan pangkalan H2 dalam memori sebelum artikel ini ditulis:

CREATE ALIAS cari_produk AS $$
import java.sql.*;
@CODE
ResultSet cari_produk(Connection conn, int idMin) throws SQLException {
    return conn.createStatement().executeQuery("SELECT id, nama, harga FROM produk WHERE id >= " + idMin);
}
$$

Kedua-duanya dipanggil dari CFML dengan cfstoredproc yang sama, jadi perbezaan hanya wujud pada peringkat penciptaan prosedur, bukan pada peringkat panggilan.

Panggilan asas: set hasil dengan cfprocresult

Set hasil ditangkap dengan cfprocresult, yang menetapkan nama pemboleh ubah query hasilnya. Blok di bawah dijalankan dalam rig dan memulangkan dua baris, dengan hasil menjadi objek query yang boleh digelungkan, diisih atau ditapis seperti hasil cfquery biasa:

<cfstoredproc procedure="cari_produk" datasource="kedai">
    <cfprocparam type="in" cfsqltype="CF_SQL_INTEGER" value="1">
    <cfprocresult name="hasil">
</cfstoredproc>
<cfoutput>Bilangan baris: #hasil.recordCount#; produk pertama: #hasil.nama[1]#</cfoutput>

cfprocresult mempunyai atribut name yang wajib, maxRows untuk mengehadkan bilangan baris, dan resultSet untuk memilih set hasil yang mana satu apabila prosedur memulangkan lebih daripada satu.

Parameter masuk, keluar dan dua hala

Parameter dihantar dengan cfprocparam, tag yang bersarang di dalam cfstoredproc. Setiap parameter mempunyai type yang menentukan arahnya: in untuk nilai yang masuk, out untuk nilai yang dipulangkan, dan inout untuk nilai yang masuk dan kemudian ditulis semula. Nilai lalai type ialah in.

Atribut cfsqltype memberitahu pemacu jenis SQL parameter itu, contohnya CF_SQL_INTEGER atau CF_SQL_VARCHAR. Dalam Lucee, cfsqltype ialah nama lama yang masih berfungsi tetapi sudah ditandakan deprecated, dan dokumentasi rasmi mengarahkan penggunaan sqltype sebagai gantinya. Kedua-duanya diuji dan kedua-duanya berfungsi. Parameter out dan inout pula menggunakan atribut variable, yang menamakan pemboleh ubah CFML yang menerima nilai selepas panggilan selesai. Bahagian ini penting: H2 tidak menyokong parameter keluar, jadi bentuk out dan inout tidak dapat diuji dalam rig dan hanya sah pada MySQL atau SQL Server.

Jadual jenis parameter

Atribut null="yes" menghantar nilai NULL dan mengabaikan value, manakala maxLength dan scale menetapkan panjang serta bilangan tempat perpuluhan parameter.

Nilai tunggal lawan set hasil

Stored procedure memulangkan salah satu daripada dua bentuk: satu set hasil yang mengandungi banyak baris, atau satu nilai skalar tunggal. Apabila prosedur memulangkan set hasil, cfprocresult memberi anda query dengan lajur bernama, jadi hasil.nama[1] memulangkan "Kopi" kerana lajur nama wujud.

Apabila prosedur memulangkan satu nilai tunggal, H2 masih menghantarnya sebagai set hasil satu baris satu lajur, dan nama lajur itu ialah nama penuh fungsi, contohnya PUBLIC.JUMLAH_PRODUK(). Nilai itu masih boleh dibaca, tetapi nama lajurnya tidak cantik, jadi baca lajur pertama tanpa bergantung pada nama: hasil[hasil.columnList][1]. Bagi MySQL, prosedur yang memulangkan nilai tunggal biasanya berbuat demikian melalui parameter out, bukan melalui set hasil satu baris. Pastikan anda tahu bentuk mana yang dipulangkan sebelum menulis kod pembaca, kerana menganggap set hasil sedangkan prosedur memulangkan nilai skalar, atau sebaliknya, menghasilkan ralat yang sukar dikesan.

Lebih daripada satu set hasil

Sesetengah prosedur memulangkan beberapa set hasil sekaligus, contohnya satu set untuk data utama dan satu lagi untuk ringkasan. Lucee menyokong ini melalui atribut resultSet pada cfprocresult, yang memilih set hasil mengikut nombor urutan dengan nilai lalai 1, jadi <cfprocresult name="perincian" resultSet="1"> diikuti <cfprocresult name="ringkasan" resultSet="2"> menangkap setiap set. Ini sangat bergantung pada pangkalan data: MySQL dan SQL Server boleh memulangkan berbilang set hasil daripada satu prosedur, tetapi alias H2 hanya memulangkan paling banyak satu ResultSet, jadi corak ini tidak dapat diuji dalam rig.

result dan returnCode

Dua atribut memberi maklumat tentang panggilan itu sendiri, bukan tentang data. Atribut result menetapkan nama satu struktur yang diisi selepas panggilan, dan returnCode menghidupkan pengisian kod status. Dalam ujian rig, result="procResult" menghasilkan struktur dengan tiga kunci sebenar: executionTime dalam mikrosaat, StatusCode dan cached.

Satu pemerhatian penting: dokumentasi lama menyebut cfstoredproc.statusCode, tetapi dalam Lucee 7.1 pemboleh ubah cfstoredproc tidak wujud, dan kod status sebenarnya dibaca daripada struktur result sebagai procResult.StatusCode. Bagi H2 nilai ini kosong kerana H2 tidak mempunyai konsep kod status prosedur seperti SQL Server, yang menggunakan RETURN untuk memulangkan integer. Jadi returnCode paling bermakna untuk SQL Server atau Sybase, dan hampir tidak berguna untuk MySQL atau H2.

Pengendalian ralat dan transaksi

Stored procedure sering mengubah data, jadi panggilan itu perlu dibalut dengan pengendalian ralat dan, apabila melibatkan beberapa langkah, satu transaksi. Corak biasa ialah membungkus cfstoredproc dalam cftry dan cfcatch, dan meletakkan keseluruhan urusan dalam cftransaction supaya kegagalan di tengah jalan membatalkan semua perubahan:

<cftransaction>
    <cftry>
        <cfstoredproc procedure="pindah_stok" datasource="kedai" result="procResult">
            <cfprocparam type="in" cfsqltype="CF_SQL_INTEGER" value="1">
            <cfprocparam type="in" cfsqltype="CF_SQL_INTEGER" value="5">
        </cfstoredproc>
        <cfcatch>
            <cftransaction action="rollback">
            <cfoutput>Gagal: #cfcatch.message#</cfoutput>
        </cfcatch>
    </cftry>
</cftransaction>

Transaksi CFML hanya bermakna jika pangkalan data menyokong transaksi dan stored procedure itu sendiri tidak melakukan COMMIT di dalam SQLnya, kerana COMMIT dalaman boleh menamatkan transaksi lebih awal daripada yang anda jangkakan. Untuk perbincangan lengkap, rujuk cftransaction, commit dan rollback.

Bahaya SQL injection

Nama prosedur dan susunan parameter mesti sentiasa ditentukan oleh kod aplikasi, bukan oleh input pengguna. Bahaya SQL injection bukan sahaja berlaku pada cfquery, tetapi juga pada cfstoredproc apabila nama prosedur atau bilangan dan susunan parameter dibina daripada nilai yang dihantar pengguna. Kod yang menyusun procedure="#form.namaProsedur#" membenarkan penyerang memilih mana-mana prosedur untuk dijalankan, atau memanggil prosedur dengan susunan parameter yang salah.

Pertahanan yang betul ialah senarai putih: petakan tindakan yang dibenarkan kepada nama prosedur tetap dalam kod, dan jangan sekali-kali menerima nama prosedur daripada parameter URL atau borang. Nilai parameter juga mesti diikat dengan jenis SQL yang betul melalui cfsqltype, bukan disambung menjadi rentetan SQL. Untuk asas pencegahan injection pada cfquery, baca cfquery dan cfqueryparam, dan untuk konteks keselamatan aplikasi web yang lebih luas, lihat keselamatan web XSS dan CSRF.

Bila stored procedure berbaloi, bila menyusahkan

Stored procedure paling berbaloi apabila logiknya berat dan perlu dikongsi antara beberapa aplikasi atau beberapa klien. Laporan bulanan yang rumit, atau peraturan stok yang mesti sama untuk aplikasi web dan aplikasi meja, lebih baik diletakkan dalam prosedur pangkalan data supaya semua pihak memanggil logik yang sama. Ia juga mengurangkan trafik rangkaian kerana enjin pangkalan data menjalankan keseluruhan logik di satu tempat dan hanya memulangkan hasil akhir.

Sebaliknya, stored procedure menyusahkan dalam tiga keadaan. Pertama, penyahpepujaan lebih sukar kerana logik tersembunyi di dalam pangkalan data dan tidak kelihatan dalam jejak ralat CFML. Kedua, versi lebih sukar diurus kerana menukar prosedur bermakna mengubah pangkalan data, yang biasanya memerlukan skrip migrasi dan penyelarasan dengan pasukan pangkalan data. Ketiga, pemindahan antara pangkalan data menjadi lebih mahal kerana sintaks prosedur MySQL tidak sama dengan SQL Server atau Oracle, jadi anda terikat dengan satu vendor. Kalau logiknya ringkas dan hanya digunakan oleh satu aplikasi, SQL dalam cfquery selalunya lebih mudah dibaca, diuji dan dipindahkan. Untuk pertimbangan prestasi, caching hasil pertanyaan mungkin memberi kesan yang lebih besar daripada memindahkan logik ke prosedur, seperti yang dibincangkan dalam caching untuk prestasi.

Soalan Lazim

Adakah cfstoredproc memerlukan sambungan tambahan dalam Lucee?

Tidak. cfstoredproc ialah tag teras Lucee dan berjalan tanpa sebarang extension. Yang menentukan kejayaan ialah pangkalan data menyokong stored procedure dan sambungan itu didaftarkan dengan betul.

Kenapa parameter out atau inout saya kosong apabila diuji dengan H2?

H2 tidak menyokong parameter out atau inout. Alias Java H2 hanya boleh memulangkan satu nilai skalar atau satu ResultSet, jadi bentuk out dan inout mesti diuji pada MySQL atau SQL Server, bukan pada H2.

Apakah perbezaan antara cfsqltype dan sqltype?

cfsqltype ialah nama lama yang masih berfungsi tetapi sudah ditandakan deprecated dalam Lucee, manakala sqltype ialah nama semasa dengan fungsi yang sama. Dokumentasi rasmi mengarahkan penggunaan sqltype, dan kedua-duanya diuji berfungsi dalam rig.

Kenapa cfstoredproc.statusCode tidak wujud?

Dokumentasi lama menyebut cfstoredproc.statusCode, tetapi dalam Lucee 7.1 pemboleh ubah itu tidak wujud. Kod status dibaca daripada struktur result, iaitu procResult.StatusCode, dan nilainya kosong untuk pangkalan data yang tidak mempunyai konsep kod status seperti H2.

Bolehkah saya membina nama prosedur daripada input pengguna?

Jangan. Membina nama prosedur daripada input pengguna membuka pintu kepada SQL injection. Gunakan senarai putih yang memetakan tindakan kepada nama prosedur tetap dalam kod.

Sumber

• Dokumentasi cfstoredproc di docs.lucee.org
• Dokumentasi cfprocparam di docs.lucee.org
• Dokumentasi cfprocresult di docs.lucee.org
• CREATE PROCEDURE di Manual MySQL
• SQL Injection di OWASP