2024.01.08(월)

🦭오늘 구현에 사용한 SQL keyword

🎯expr IN (value,...)

• expr이 IN list의 어떤 값과 같다면 1 반환 그렇지 않으면 0 반환
• IN list의 요소들이 모두 상수라면 binary search가 수행되기 때문에 매우 빠름
• 그렇지 않으면 모든 요소에 대해 Type Conversion이 적용됨
• expr가 NULL이면 항상 NULL을 반환함
• IN list에 NULL이 있는 경우, 하나라도 같은 값이 있다면 1 반환 그렇지 않으면 NULL 반환

SELECT 2 IN (0,3,5,7);
--
+----------------+
| 2 IN (0,3,5,7) |
+----------------+
|              0 |
+----------------+

SELECT 'wefwf' IN ('wee','wefwf','weg');
--
+----------------------------------+
| 'wefwf' IN ('wee','wefwf','weg') |
+----------------------------------+
|                                1 |
+----------------------------------+ 

/* Type conversion */
SELECT 1 IN ('1', '2', '3');
--
+----------------------+
| 1 IN ('1', '2', '3') |
+----------------------+
|                    1 |
+----------------------+

SELECT NULL IN (1, 2, 3);
--
+-------------------+
| NULL IN (1, 2, 3) |
+-------------------+
|              NULL |
+-------------------+

SELECT 1 IN (1, 2, NULL);
--
+-------------------+
| 1 IN (1, 2, NULL) |
+-------------------+
|                 1 |
+-------------------+

SELECT 5 IN (1, 2, NULL);
--
+-------------------+
| 5 IN (1, 2, NULL) |
+-------------------+
|              NULL |
+-------------------+

❓Escaping query values with placeholder

The purpose of escaping input is to avoid SQL Injection attacks. 🔗

📍.query()의 두 가지 형식

1. .query(sqlString, callback)
2. .query(sqlString, values, callback)

• placeholder values를 이용하려면 두 번째 형식을 사용함
• sqlString에서 replacement character를 사용하고, 두 번째 인자인 values로 들어갈 값을 넣어주면 됨

📍기본 사용법

• replacement character ? & ??

  • 일반적인 user input value → ? 사용

  • SQL identifier(database/table/column name)를 사용자로부터 받는 경우 → ?? 사용

    var userId = 1;
    var columns = ['username', 'email'];
    var query = connection.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId], function (error, results, fields) {
      if (error) throw error;
      // ...
    });
    
    console.log(query.sql); // SELECT `username`, `email` FROM `users` WHERE id = 1

• replacement character를 하나만 사용하는 경우
  • 값이 null이나 undefined가 아니라면, 두 번째 인자로 바로 넘겨줄 수 있음

    connection.query(
      'SELECT * FROM `books` WHERE `author` = ?',
      'David',
      function (error, results, fields) {
        // error will be an Error if one occurred during the query
        // results will contain the results of the query
        // fields will contain information about the returned results fields (if any)
      }
    );

  • 또는 array에 담아서 넘겨주기

    connection.query('SELECT * FROM users WHERE id = ?', [userId], function (error, results, fields) {
      if (error) throw error;
      // ...
    });

• 여러 개의 replacement character를 사용하는 경우
  • ? 개수 만큼 순서대로 mapping됨

    connection.query('UPDATE users SET foo = ?, bar = ?, baz = ? WHERE id = ?', ['a', 'b', 'c', userId], function (error, results, fields) {
      if (error) throw error;
      // ...
    });

📍다양한 value type에 따른 escape 방식

value type	converted to	example
Date 객체	'YYYY-mm-dd HH:ii:ss’	new Date('2024-01-08') → '2024-01-08 00:00:00’
Array	list	['a', 'b'] → 'a', 'b'
Nested array	grouped list (for bulk inserts)	[['a', 'b'], ['c', 'd']] → ('a', 'b'), ('c', 'd')
객체	key = 'val' pair ▸property의 value가 function인 경우 skip ▸property의 value가 object인 경우 toString()을 호출해서 반환된 값 사용	{id: 1, title: 'Hello MySQL'} → id = 1, title = 'Hello MySQL'
tosqlString method를 가진 객체	.tosqlString() method를 호출해서 반환된 값을 raw SQL로 사용
undefined / null	NULL
NaN / Infinity	그대로 NaN / Infinity ※ MySQL는 이 값을 지원하지 않으며, 삽입하려고 하면 MySQL 오류가 발생

var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function (error, results, fields) {
  if (error) throw error;
  // Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

var CURRENT_TIMESTAMP = { toSqlString: function() { return 'CURRENT_TIMESTAMP()'; } };
var sql = mysql.format('UPDATE posts SET modified = ? WHERE id = ?', [CURRENT_TIMESTAMP, 42]);
console.log(sql); // UPDATE posts SET modified = CURRENT_TIMESTAMP() WHERE id = 42

➕ 기본적으로 unnamed placeholder를 사용하지만 namedPlaceholders라는 config value를 setting해주면 named placeholder도 사용 가능

connection.config.namedPlaceholders = true;
connection.execute('select :x + :y as z', { x: 1, y: 2 }, (err, rows) => {
  // statement prepared as "select ? + ? as z" and executed with [1,2] values
  // rows returned: [ { z: 3 } ]
});

참고 : Extra Features | Quickstart

✅API에 적용

• Array가 list로 convert되는 escape 방식을 활용하여 IN list를 넘겨줌
• 관련 stack overflow 글

const getCartItems = (req, res) => {
    const { userId, selected } = req.body;

    let sql = `
        SELECT
            item_id,
            id AS book_id,
            title,
            summary,
            quantity,
            price
        FROM cart
        LEFT JOIN books
        ON cart.book_id = books.id
        WHERE user_id = ?
    `;
    const values = [userId];
    if (selected) {
        sql += "AND item_id IN (?)";
        values.push(selected);
    }
    conn.query(
        sql, values,
        (err, results) => {
            if (err) {
                console.log(err);
                return res.status(StatusCodes.BAD_REQUEST).end();
            }

            return res.status(StatusCodes.OK).json(results);
        }
    )
};

오늘 새롭게 IN keyword를 사용하면서 node.js mysql module의 placeholder에 대해서도 자세히 알아보는 시간을 가질 수 있었다. PR을 통해 팀원의 코드에 comment를 달아봤는데 PR suggestion 기능이 정말 좋은 것 같다. 코드 리뷰 과정에서 팀원들의 코드를 참고하여 내 코드를 어떻게 수정하면 좋을지 생각해볼 수 있어 유익했다.