不動の鳥の勉強記録

時間があるときに勉強したことをメモします。

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.”

以上。