[ Day 5 ]

說明:
建立一個商業動態網站，和資料庫互動，讀存刪改資料是必不可少的
因此今天將學習下使用套件和本機的mysql資料庫連結，並且進行簡單的CRUD指令

預先準備:
已經有一套可以連線的mysql資料庫和帳號密碼，且有相關資料表。

一、使用mysql套件和資料庫連結

var mysql = require('mysql');

var pool = mysql.createPool({
  user: 'root',
  password: '123456',
  host: 'localhost',
  port: '3306',
  database: 'demo',
  waitForConnections : true,
  connectionLimit : 10 //連線數上限
})

注意:此為學習用寫法，正式在開發時，將連線資訊寫在程式碼裡非常危險
需改為config設定方式配置，並加密

二、native select語法

// 取得連線
pool.getConnection((err, connection) => {

  if (err) {
    console.log(err)
  } else {
    connection.query( 'SELECT * FROM user', 
      function(err, rows) {
        //callback function
        console.log(rows)
        // console.log(err)
        // 釋放連線
        connection.release();
    });
  }
});

將原生的sql語句以參數形式傳入query方法
在callback function中取得row set
結果:

三、增加查詢條件

connection.query( 'SELECT * FROM user where user_name = ' + param , 
      function(err, rows) {
        //callback function
        console.log(rows)
        // console.log(err)
        // 釋放連線
        connection.release();
    });

注意: sql injection
上例雖然可以成功撈取到特定的欄位條件，但是卻會造成SQL injection
假如我們將param 傳入

"'1'  or '' = ''"

全部的資料就被撈出來了，所以這種寫法在正式專案中是不能使用的

三之一、prepared statement
避免sql injection，改用prepared statement，將查詢欄位的資料先以佔位符固定下來，以防語句的串接

 connection.query( 'SELECT * FROM user where user_name = ? ', [param] , 
      function(err, rows) {
        //callback function
        console.log(rows)
        // console.log(err)
        // 釋放連線
        connection.release();
    });

再次帶入"'1' or '' = ''"

四、新增資料

pool.getConnection((err, connection) => {

  if (err) {
    console.log(err)
  } else {
    connection.query( 'INSERT INTO user(user_name) values (?) ', [param] , 
      function(err, rows) {
        //callback function
        console.log(rows)
        // console.log(err)
        // 釋放連線
        connection.release();
    });
  }
});

可以看到成功新增後，回傳的自增主鍵值 insertId是15
這個id在某些需要複合步驟操作的情況下，會需要用到

五、修改資料

pool.getConnection((err, connection) => {

  if (err) {
    console.log(err)
  } else {
    connection.query( 'UPDATE user set user_name = ? where user_id = ?', [param, userId] , 
      function(err, rows) {
        //callback function
        console.log(rows)
        // console.log(err)
        // 釋放連線
        connection.release();
    });
  }
});

六、刪除資料

pool.getConnection((err, connection) => {

  if (err) {
    console.log(err)
  } else {
    connection.query( 'DELETE FROM user where user_id = ?', [userId] , 
      function(err, rows) {
        //callback function
        console.log(rows)
        // console.log(err)
        // 釋放連線
        connection.release();
    });
  }
});

對資料庫有略為了解的朋友會曉得，不同的sql資料庫會有其專用的sql方言
另外在程式碼中管控sql語句字串也是非常不好維護的一件事
因此day6將介紹Knex.js，是一套Query Builder的nodejs套件
可以避免掉上述所說的那兩項問題

Day5結束