Node.jsでSQLServer2017に接続してDBへレコードをINSERTする画面サンプル
昨日はDBへSELECTした結果を画面に表示しました。
今回はDBへINSERTしたいと思います。
Express Generatorでテンプレートを作成しています。
■環境
OS: Windows 10 Home 64bit
DB: SQLServer2017 Developer Edition
node: v8.9.1
npm: 4.0.5
■アウトライン
1. DBへINSERTする内容を入力するフォームを作成する。
2. フォームから送られてきたデータをDBへINSERTする。
3. DBへINSERTした後に、INSERTされていることを確認する画面へリダイレクトする。
■1. DBへINSERTする内容を入力するフォームを作成する。
ejsでフォーム(form.ejs)を下記のように作成し、viewsフォルダの配下に配置します。
今回は下記のように「NAME」と「MAIL」の2つのテキストボックスを用意しました。
<!DOCTYPE html> <html lang="ja"> <head> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> <title><%= title %></title> <link rel='stylesheet' href='/stylesheets/style.css' /> </head> <body> <head><h1><%= title %></h1></head> <div role="main"> <form method="post" action="/sample/submit"> <table> <tr><th>NAME</th><td><input type="text" name="name"></td></tr> <tr><th>MAIL</th><td><input type="text" name="mail"></td></tr> <tr><th></th><td><input type="submit" value="登録"></td></tr> </table> </form> </div> </body> </html>
■2. フォームから送られてきたデータをDBへINSERTする。
DBへフォームの内容をINSERTするスクリプトを作成します。(sample.js)
下記のようにINSERTするロジックを作成しました。
var express = require('express'); var router = express.Router(); // Connectionを定義する var Connection = require('tedious').Connection; // SQLServerの接続定義を記載する。 var config = { userName: 'DB接続するアカウント', password: 'DB接続するアカウントのパスワード', server: 'DBサーバのIPアドレス', } // フォームを表示する // http://localhost:3000/sample/form へのアクセスでフォームを表示する router.get('/form', function(req, res, next) { res.render('form', { title: 'sqlserver-sample-form'}); }); // レコードを追加する // フォームからSUBMITされた際にレコードを追加する router.post('/submit', function(req, res, next) { var connection = new Connection(config); // DB接続した際のイベントハンドラ connection.on('connect', function(err){ console.log("connected"); submitStatement(); }); // DB接続を終了した際のイベントハンドラ // DB接続を切断したことを確認する connection.on('end', function(){ console.log("disconnected"); }); var Request = require('tedious').Request; var TYPES = require('tedious').TYPES; // SQLを発行する関数 function submitStatement(){ // 発行するSQLを記載する request = new Request("INSERT INTO TestDB.dbo.TB_TestTable (NAME, MAIL) VALUES (@name, @mail);", function(err){ if(err){ console.log(err);} }); // フォームから入力された値をパラメータにセットする。 request.addParameter('name', TYPES.VarChar, req.body.name); request.addParameter('mail', TYPES.VarChar, req.body.mail); // SQLの行ごとに実行するイベントハンドラ request.on('row', function(columns){ columns.forEach(function(column){ if(column.value === null){ console.log('NULL'); }else{ console.log('INSERT ' + column.value); } }); }); // SQLのリクエスト完了時のイベントハンドラ。 // コネクションをクローズしないとDBにいらないプロセスが残るので、コネクションをクローズする。 request.on('requestCompleted', function(){ console.log('requestCompleted'); connection.close(); }); // SQLのリクエスト完了時のイベントハンドラ。 // コネクションをクローズしないとDBにいらないプロセスが残るので、コネクションをクローズする。 request.on('done', function(){ console.log('done'); connection.close(); }); // DBへSQLを発行する。 connection.execSql(request); } }); module.exports = router;
ここまでだと、フォームからデータを送信しても送信結果が見れないので、
送信後にSELECTした結果を表示する画面へリダイレクトをさせます。
■3. DBへINSERTした後に、INSERTされていることを確認する画面へリダイレクトする。
先ほど作成したsample.jsにまとめて書いてしまいます。
var express = require('express'); var router = express.Router(); // Connectionを定義する var Connection = require('tedious').Connection; // SQLServerの接続定義を記載する。 var config = { userName: 'DB接続するアカウント', password: 'DB接続するアカウントのパスワード', server: 'DBサーバのIPアドレス', } /* GET users listing. */ router.get('/list', function(req, res, next) { var connection = new Connection(config); var content = []; // DBからselectした結果を格納する変数 // DB接続した際のイベントハンドラ connection.on('connect', function(err){ console.log("connected"); executeStatement(); }); // DB接続を終了した際のイベントハンドラ // DB接続を切断した後に画面を描写する connection.on('end', function(){ console.log("disconnected"); res.render('list', { title: 'sqlserver-sample', content: content }); }); var Request = require('tedious').Request; // SQLを発行する関数 function executeStatement(){ // 発行するSQLを記載する request = new Request("SELECT * FROM TestDB.dbo.TB_TestTable with (NOLOCK)", function(err){ if(err){ console.log(err);} }); var result = {}; // SQLの結果を行ごとにオブジェクトに格納する。 // SQLの行ごとに実行するイベントハンドラ request.on('row', function(columns){ columns.forEach(function(column){ if(column.value === null){ console.log('NULL'); }else{ result[column.metadata.colName] = column.value; } }); content.push(result); result = {}; }); // SQLのリクエスト完了時のイベントハンドラ。 // コネクションをクローズしないとDBにいらないプロセスが残るので、コネクションをクローズする。 request.on('requestCompleted', function(){ console.log('requestCompleted'); connection.close(); }); // DBへSQLを発行する。 connection.execSql(request); } }); // フォームを表示する router.get('/form', function(req, res, next) { res.render('form', { title: 'sqlserver-sample-form'}); }); // レコードを追加する router.post('/submit', function(req, res, next) { var connection = new Connection(config); // DB接続した際のイベントハンドラ connection.on('connect', function(err){ console.log("connected"); submitStatement(); }); // DB接続を終了した際のイベントハンドラ // DB接続を切断した後に画面を描写する connection.on('end', function(){ console.log("disconnected"); res.redirect('/sample/list'); }); var Request = require('tedious').Request; var TYPES = require('tedious').TYPES; // SQLを発行する関数 function submitStatement(){ // 発行するSQLを記載する request = new Request("INSERT INTO TestDB.dbo.TB_TestTable (NAME, MAIL) VALUES (@name, @mail);", function(err){ if(err){ console.log(err);} }); request.addParameter('name', TYPES.VarChar, req.body.name); request.addParameter('mail', TYPES.VarChar, req.body.mail); // SQLの行ごとに実行するイベントハンドラ request.on('row', function(columns){ columns.forEach(function(column){ if(column.value === null){ console.log('NULL'); }else{ console.log('INSERT ' + column.value); } }); }); // SQLのリクエスト完了時のイベントハンドラ。 // コネクションをクローズしないとDBにいらないプロセスが残るので、コネクションをクローズする。 request.on('requestCompleted', function(){ console.log('requestCompleted'); connection.close(); }); // SQLのリクエスト完了時のイベントハンドラ。 // コネクションをクローズしないとDBにいらないプロセスが残るので、コネクションをクローズする。 request.on('done', function(){ console.log('done'); connection.close(); }); // DBへSQLを発行する。 connection.execSql(request); } }); module.exports = router;
最後にapp.jsにルーティングスクリプトなどを追加しておきます。
var sample = require('./routes/sample'); /* 中略 */ app.use('/sample', sample)
これで準備は完了です。起動してみます。
npm start
http://localhost:3000/sample/list へアクセスするとDBへSELECTした結果が表示され、
http://localhost:3000/sample/form へアクセスするとフォームが表示され「送信」ボタンを押下すると、
DBへレコードが追加され、http://localhost:3000/sample/listへリダイレクトされます。
■ソースコード
ソースコードはGithubにコミットしました。DBの定義は、「Sample1-SQL」のSQLから作成してください。
github.com
■所見
フォームなどから値入力がった場合は、SQLインジェクション対策を行うのが一般的です。
Microsoft様の公式サイトに下記記載があるサンプルをもとにDBへレコードをINSERTしているので、
SQLインジェクション対策できている!と信じたいです。
これでDBへのINSERTとSELECTができるようになったので、簡単なアプリケーションなら作成できる準備ができたと思いたい。
Microsoft様サイトからの引用
”In this example you will see how to execute an INSERT statement safely, pass parameters which protect your application from SQL injection value.”
以上。