[Python][MySQL] SQL query 전달 시 주의사항 feat. REGEXP

작성일: 2024-09-27

 

 

 

상황

---

 Python 프로그램에서 외장 라이브러리 sshtunnel, pymysql을 이용해서

RDS에 연결해서 데이터를 적재하고 있다.

필요할 때는 특정 데이터를 조회해서 엑셀로 추출하는데, 이번에 'DatabaseError'가 발생했다.

DatabaseError 발생

 

사실 전에도 발생했던 건데

이유는 SQL syntax 문제로, 내가 작성한 쿼리에 문제가 있어 DB가 이해 못했다는 의미이다.

문제가 있는 구문은 '|&|x|x|X|\\+|/' 가 있는 부분이다.

1064 error 상세 메시지

 

 

이는 내가 DB 쿼리에서 데이터가 있나 확인한 후

그대로 파이썬 프로그램 쿼리문 부분에 복사 붙여넣기하는 방식 때문에 생긴건데,

평소에는 간단한 조회를 많이 하나

간혹 REGEXP(정규 표현식)를 할 때 이런 문제가 생긴다.

MySQL(혹은 MariaDB)에서도 특수기호가 특수한 의미를 가지고 쓰기 때문에

특수기호인 걸 명확히 하기 위해 escape 기호인 '\'를 써줘야한다.

그런데 어떤 기호에서 escape 기호를 안 써서 문제가 생긴지 모르니까 전부 다 적용해줄 수도 있지만

(가령, '\|\&\|\x\|\x\|\X\|\\+\|\/' 이렇게)

하다가 빼먹을 수도 있고 헷갈리기 때문에 그냥 r-string을 해주기로 한다.

그럼 아주 간단히 해결됨.

 

 

 

해결

---

Before - 결과: DatabaseError

query = '''
SELECT * 
FROM TABLE
WHERE COL1 > 15 
AND COL1 <= 100
AND COL2 REGEXP "|&|x|x|X|+|/"
ORDER BY COL3
'''

 

 

 

After - 결과: 성공!

query = r'''
SELECT * 
FROM TABLE
WHERE COL1 > 15 
AND COL1 <= 100
AND COL2 REGEXP "|&|x|x|X|+|/"
ORDER BY COL3
'''

 

 

 

돌아보며

---

나처럼 간단히 엑셀 추출용으로 프로그램 코딩 시에는

query문을 직접 써도 좋지만

실제 서비스에 이용되는 것은 쿼리문을 직접 작성하기보다는 

ORM을 쓰거나 필요한 쿼리문을 작성하고 변수에 해당하는 값은 따로 연결하는 파라미터 바인딩을 통해 Prepared statement을 이용하는 것이 보안이나 유지보수상 더 낫다.

 

c.f. GPT에게 예시 부탁한 결과

직접 쿼리를 작성할 때는 SQL 인젝션 같은 공격에 취약할 수 있기 때문입니다. 이를 방지하기 위해 보통 다음과 같은 방법들을 사용합니다:

1. ORM(Object-Relational Mapping) 사용

ORM은 데이터베이스의 테이블을 클래스와 객체로 매핑하여 직접적인 SQL 쿼리 없이 데이터베이스와 상호작용할 수 있게 해줍니다. 예를 들어, Django의 ORM이나 SQLAlchemy를 사용하면 SQL 문법을 사용하지 않고 파이썬 코드만으로 데이터베이스와 통신할 수 있어 안전합니다.

예시:

# SQLAlchemy 사용 예
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from my_model import User  # User는 데이터베이스 테이블을 나타내는 클래스

engine = create_engine("sqlite:///example.db")
Session = sessionmaker(bind=engine)
session = Session()

# 간단한 조회 쿼리
user = session.query(User).filter_by(name="John").first()
print(user.email)

 

2. Prepared Statement (Prepared Statements)

Prepared Statement를 사용하면 입력값을 미리 정의된 템플릿에 삽입하여 SQL 인젝션을 막을 수 있습니다. 파이썬의 sqlite3 라이브러리나 psycopg2 (PostgreSQL) 모듈은 Prepared Statement를 지원합니다.

예시:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Prepared Statement 사용
cursor.execute("SELECT * FROM users WHERE name=?", ("John",))
print(cursor.fetchall())

3. Stored Procedures

데이터베이스에 저장된 프로시저를 호출하여 데이터를 처리하는 방법입니다. 쿼리 논리를 애플리케이션 코드가 아닌 데이터베이스 내에서 관리하기 때문에 SQL 인젝션을 방지할 수 있습니다.

예시 (MySQL에서):

import pymysql

connection = pymysql.connect(host='localhost',
                             user='user',
                             password='password',
                             database='db_name')
cursor = connection.cursor()

# 저장 프로시저 호출
cursor.callproc('GetUserByName', ('John',))
result = cursor.fetchall()
print(result)

 

이러한 방법들은 데이터베이스와의 직접적인 쿼리 작성 대신, 보안 및 유지보수를 더 쉽게 해주고 SQL 인젝션과 같은 취약점을 방지할 수 있게 도와줍니다. ORM이나 Prepared Statement가 가장 많이 사용되는 방식이니, 먼저 이를 고려해보세요.