문제 상황
여러 개의 카드 리스트를 한꺼번에 insert하고 싶었다.
찾아보니 bulk insert라는 기능이 있어 mysql2에서 지원하는 방식대로 데이터를 한꺼번에 전달해주려고 했다.
const cardLists = [
{ id: 1, name: 'To Do' },
{ id: 2, name: 'Doing' },
{ id: 3, name: 'Done' },
]
const insertCardListSQL = `INSERT INTO card_list (id, name) VALUES ?`
await DB.execute(insertCardListSQL, [cardLists.map(({ id, name }) => [id, name])])실행했더니 다음과 같은 에러가 났다.
Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1
{
code: 'ER_PARSE_ERROR',
errno: 1064,
sql: 'INSERT INTO card_list (id, name) VALUES ?',
sqlState: '42000',
sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1"
}이상했다. mysql2의 format으로 raw SQL을 만들어 보면 문법 오류가 없는 SQL문이 생성되는데, execute를 호출하면 에러가 났다.
구글링해서 github issue를 발견했다. prepared statements를 만들 때 placeholder에 전달될 데이터의 개수를 몰라서 안된다는 식의 답변이 적혀있다.
prepared statement가 무엇인지를 알아봐야 이해할 수 있겠다.
Prepared Statements
Prepared Statements는 SQL문의 데이터 부분에 placeholder라고 부르는 ?이 들어간 불완전한 statement이다. ? 부분에 들어갈 데이터는 분리하여 보관, 전달할 수 있다.
Prepared Statements를 사용하면 얻을 수 있는 이점으로 크게 2가지가 있다. SQL Injection 공격 방지, 성능 향상이다. 이를 이해하기 위해서는 DBMS가 SQL 쿼리를 처리하는 과정을 알아야 한다.
DBMS는 SQL 쿼리를 실행할 때 다음과 같은 단계를 거친다.
1. Parsing and Normalization Phase
2. Compilation Phase
3. Query Optimization Plan
4. Cache
5. Execution
Prepared Statements가 전달되면, 1단계에서 4단계까지 진행된다. placeholder에 들어갈 데이터가 오지 않았기 때문에, 컴파일된 쿼리 상태로 캐싱되어있다가 데이터가 전달되면 데이터만 조립하여 바로 실행시킬 수 있는 상태가 되는 것이다.
Prepared Statements가 동일하면 캐싱되어있는 컴파일된 쿼리를 가져다 사용할 수 있다. 데이터만 끼워 넣고 실행시키면 된다. 1~3단계를 거치지 않으므로 효율적이다. => 성능 향상
SQL문을 파싱하고 컴파일하는 과정은 1, 2단계에서 일어난다. 이미 컴파일된 prepared statements에 SQL Injection을 유도하는 문자열을 집어넣어도, 이미 구문 분석이 끝났기 때문에 공격자가 유도한 SQL은 실행될 여지가 전혀 없다. => SQL Injection 공격 방지
결론
mysql2의 query 함수에서 사용 가능한 bulk insert는 MySQL이나 SQL의 문법이 아니고, mysql2에서 사용자 편의를 위해 만든 기능이다.(? 하나만 넣고 3차원 배열 전달하는 것)
mysql2의 execute는 쿼리를 prepare하고, 데이터를 전달하고, 최종적으로 실행시키는 과정을 하나의 함수로 합친 것이다.
애초에 INSERT INTO card_list (id, name) VALUES ?는 올바는 SQL 문법이 아니며, 배열로 전달될 데이터의 길이가 얼마가 될 지는 쿼리를 prepare할 때 체크하지 않기 때문에 하나뿐인 ?를 미리 데이터 수에 맞게 추가할 수 없는 것 같다.
그래서 bulk insert 할 때는 execute 대신 query를 사용하던지, 데이터의 수를 미리 아는 상황이라면 SQL문에 데이터 수만큼 ?를 넣어주어야 한다.
mysql2를 사용하면 query를 사용해도 SQL Injection에 대응하기 위해 데이터에 대해 escape를 수행하기 때문에, 성능 목적이 아니면 그냥 query를 사용해도 좋을 것 같다. 성능 목적 상 bulk insert에 prepared statements 사용을 고려한다면, 데이터의 수가 유동적일 땐 ?의 개수도 변해야 하므로 캐싱이 무용지물이 된다는 점을 유념해야 할 것이다.
참고 자료
