SQL Escape Function
์นํดํน ๋ฌธ์ ๋ค์ ๋ค์ ํ๋ฉด์ ๋ฐ๋๋ก ๋ง๋๋ค๋ฉด ์ด๋ป๊ฒ ๋ง์๊น, ์๋๋ฉด ์ด๋ป๊ฒ ๋ผ์์ผ๋ฉด ์ ๋ง๋ก ๋ซ๊ธฐ ํ๋ค๊น์ ๋ํ ๊ณ ๋ฏผ์ ๊ธฐ๋ฐ์ผ๋ก ๋ง๋ ํจ์๋ค.
์ ๊ท์์ผ๋ก ๋ค์ด์ค๋ ์ธ์์ ๋ํด ํ์์ด ๋ง๋์ง, ๊ธธ์ด๋ ์ ์ ํ์ง 1์ฐจ์ ์ผ๋ก ๊ฒ์ฆํ๊ณ ๊ทธ ๋ค์ ์ด ํจ์๋ฅผ ์ฌ์ฉํ๋ ๊ฒ์ ๊ถ์ฅํ๋ค.
SQL ๋ฌธ์ ์์ฃผ ์ฌ์ฉ๋ ์ผ์ด ์์ผ๋ฉด์, SQL Injection ๊ณต๊ฒฉ์๋ ์์ฃผ ์ฌ์ฉ๋๋ ๋ฌธ์๋ค์ ๋ณํํ๋ค.
Python, JavaScript, PHP ์ธ๊ฐ์ง ์ธ์ด์ ๋์ํ ์ ์๊ฒ ๋ง๋ค์๊ณ ์
๋ค ์ง๊ด์ ์ผ๋ก ์์ ๋ฐ ์ถ๊ฐํ ์ ์๋๋ก replace ํจ์ ์ค์ฌ์ผ๋ก ๊ตฌ์ฑํ๋ค.
์ค์ ๋ก ์ฌ์ฉ๋๋ ํจ์๋ ํน์๋ฌธ์๊ฐ ์๋ค๋ฉด ํด๋น ์ค์ ์ฃผ์์ฒ๋ฆฌํด์ ์ฌ์ฉํด์ผํ๋ค.
NoSQL์ธ MongoDB์ ๊ฐ์ ํํ๊ฐ ์๋๋ผ๋ฉด ์ฌ๋งํ๋ฉด Prepared Statements๋ฅผ ์ฌ์ฉํ๋ ๊ฒ์ด ์์ ํ๋ค.
Python
import re
def sqlescape(value):
if re.match(r"^0x[0-9a-f]+$", value, re.IGNORECASE): # hex format check
return False
if re.match(r"^b[01]+$", value, re.IGNORECASE): # binary formatcheck
return False
value = value.replace("%", "\\%") # if use %, disable
"""
value = value.replace("%00", "\\%00") #null
value = value.replace("%09", "\\%09") #\t
value = value.replace("%0a", "\\%0a") #\n
value = value.replace("%0d", "\\%0d") #\r
value = value.replace("%20", "\\%20") #space
"""
value = value.replace("\0", "\\0")
value = value.replace("\x08", "\\b")
value = value.replace("\x09", "\\t")
value = value.replace("\x1a", "\\Z")
value = value.replace("\n", "\\n")
value = value.replace("\r", "\\r")
value = value.replace("\"", "\\\"")
value = value.replace("'", "\\'")
value = value.replace("\\", "\\\\")
value = value.replace("|", "\\|")
value = value.replace("&", "\\&")
value = value.replace("#", "\\#")
value = value.replace("+", "\\+")
value = value.replace(";", "\\;")
value = value.replace(">", "\\>")
value = value.replace("<", "\\<")
value = re.sub(r"sleep\(", "sleep\\(", value, flags=re.I)
value = re.sub(r"benchmark\(", "benchmark\\(", value, flags=re.I)
value = re.sub(r"analyse\(", "analyse\\(", value, flags=re.I)
value = re.sub(r"concat\(", "concat\\(", value, flags=re.I)
value = re.sub(r"union\(", "union\\(", value, flags=re.I)
value = re.sub(r"select\(", "select\\(", value, flags=re.I)
value = re.sub(r"update\(", "update\\(", value, flags=re.I)
value = re.sub(r"delete\(", "delete\\(", value, flags=re.I)
value = re.sub(r"insert\(", "insert\\(", value, flags=re.I)
value = re.sub(r"create\(", "create\\(", value, flags=re.I)
value = re.sub(r"drop\(", "drop\\(", value, flags=re.I)
value = re.sub(r"alter\(", "alter\\(", value, flags=re.I)
value = re.sub(r"rename\(", "rename\\(", value, flags=re.I)
value = re.sub(r"truncate\(", "truncate\\(", value, flags=re.I)
value = re.sub(r"replace\(", "replace\\(", value, flags=re.I)
value = re.sub(r"load_file\(", "load_file\\(", value, flags=re.I)
value = re.sub(r"outfile\(", "outfile\\(", value, flags=re.I)
value = re.sub(r"dumpfile\(", "dumpfile\\(", value, flags=re.I)
value = re.sub(r"substring\(|substr\(", "substr\\(", value, flags=re.I)
value = re.sub(r"ascii\(", "ascii\\(", value, flags=re.I)
value = re.sub(r"mid\(", "mid\\(", value, flags=re.I)
value = re.sub(r"char\(", "char\\(", value, flags=re.I)
value = re.sub(r"hex\(", "hex\\(", value, flags=re.I)
value = re.sub(r"bin\(", "bin\\(", value, flags=re.I)
value = re.sub(r"version\(", "version\\(", value, flags=re.I)
value = re.sub(r"database\(", "database\\(", value, flags=re.I)
value = re.sub(r"user\(", "user\\(", value, flags=re.I)
value = re.sub(r"schema\(", "schema\\(", value, flags=re.I)
value = re.sub(r"table\(", "table\\(", value, flags=re.I)
value = re.sub(r"column\(", "column\\(", value, flags=re.I)
value = re.sub(r"procedure\(", "procedure\\(", value, flags=re.I)
value = re.sub(r"function\(", "function\\(", value, flags=re.I)
value = re.sub(r"where\(", "where\\(", value, flags=re.I)
value = re.sub(r"having\(", "having\\(", value, flags=re.I)
value = re.sub(r"order\(", "order\\(", value, flags=re.I)
value = re.sub(r"group\(", "group\\(", value, flags=re.I)
value = re.sub(r"by(", "by\(", value, flags=re.I)
value = re.sub(r"desc(", "desc\(", value, flags=re.I)
value = re.sub(r"asc(", "asc\(", value, flags=re.I)
value = re.sub(r"reverse(", "reverse\(", value, flags=re.I)
value = re.sub(r"admin", "", value, flags=re.I | re.B)
value = re.sub(r"\bhex\b|\bbinary\b", "false", value, flags=re.I)
return value
Python
๋ณต์ฌ
JavaScript
function sqlescape(value) {
if (/^0x[0-9a-f]+$/i.test(value)) { // hex format check
return false;
}
if (/^b[01]+$/i.test(value)) { // binary format check
return false;
}
value = value.replace(/[%]/g, "\\%"); // if use %, disable
/*
value = value.replace(/%00/g, "\\%00"); // null
value = value.replace(/%09/g, "\\%09"); // \t
value = value.replace(/%0a/g, "\\%0a"); // \n
value = value.replace(/%0d/g, "\\%0d"); // \r
value = value.replace(/%20/g, "\\%20"); // space
*/
value = value.replace(/[]/g, "\\0");
value = value.replace(/[\0]/g, "\\0");
value = value.replace(/[\x08]/gi, "\\b");
value = value.replace(/[\x09]/gi, "\\t");
value = value.replace(/[\x1a]/gi, "\\Z");
value = value.replace(/[\n]/gi, "\\n");
value = value.replace(/[\r]/gi, "\\r");
value = value.replace(/["]/g, '\\"');
value = value.replace(/[']/g, "\\'");
value = value.replace(/[\\]/g, "\\\\");
value = value.replace(/[|]/g, "\\|");
value = value.replace(/[&]/g, "\\&");
value = value.replace(/[#]/g, "\\#");
value = value.replace(/[+]/g, "\\+");
value = value.replace(/[;]/g, "\\;");
value = value.replace(/[/*]/g,"\\/*");
value = value.replace(/[--]/g,"\\--");
value = value.replace(/[<]/g,"\\<");
value = value.replace(/[>]/g,"\\>");
value = value.replace(/sleep\(/gi, "sleep\\(");
value = value.replace(/benchmark\(/gi, "benchmark\\(");
value = value.replace(/analyse\(/gi, "analyse\\(");
value = value.replace(/concat\(/gi, "concat\\(");
value = value.replace(/union\(/gi, "union\\(");
value = value.replace(/select\(/gi, "select\\(");
value = value.replace(/update\(/gi, "update\\(");
value = value.replace(/delete\(/gi, "delete\\(");
value = value.replace(/insert\(/gi, "insert\\(");
value = value.replace(/create\(/gi, "create\\(");
value = value.replace(/drop\(/gi, "drop\\(");
value = value.replace(/alter\(/gi, "alter\\(");
value = value.replace(/rename\(/gi, "rename\\(");
value = value.replace(/truncate\(/gi, "truncate\\(");
value = value.replace(/replace\(/gi, "replace\\(");
value = value.replace(/load_file\(/gi, "load_file\\(");
value = value.replace(/outfile\(/gi, "outfile\\(");
value = value.replace(/dumpfile\(/gi, "dumpfile\\(");
value = value.replace(/sub(?:str)?\(/gi, "substr\\(");
value = value.replace(/ascii\(/gi, "ascii\\(");
value = value.replace(/mid\(/gi, "mid\\(");
value = value.replace(/char\(/gi, "char\\(");
value = value.replace(/hex\(/gi, "hex\\(");
value = value.replace(/bin\(/gi, "bin\\(");
value = value.replace(/version\(/gi, "version\\(");
value = value.replace(/database\(/gi, "database\\(");
value = value.replace(/user\(/gi, "user\\(");
value = value.replace(/schema\(/gi, "schema\\(");
value = value.replace(/table\(/gi, "table\\(");
value = value.replace(/column\(/gi, "column\\(");
value = value.replace(/procedure\(/gi, "procedure\\(");
value = value.replace(/function\(/gi, "function\\(");
value = value.replace(/where\(/gi, "where\\(");
value = value.replace(/having\(/gi, "having\\(");
value = value.replace(/order\(/gi, "order\\(");
value = value.replace(/group\(/gi, "group\\(");
value = value.replace(/by\(/gi, "by\\(");
value = value.replace(/desc\(/gi, "desc\\(");
value = value.replace(/asc\(/gi, "asc\\(");
value = value.replace(/reverse\(/gi, "reverse\\(");
return value;
}
JavaScript
๋ณต์ฌ
PHP
function sqlescape($value) {
if (/^0x[0-9a-f]+$/i.test(value)) { // hex format check
return false;
}
if (/^b[01]+$/i.test(value)) { // binary format check
return false;
}
$value = str_replace("%", "\\%", $value); // if use %, disable
/*
$value = str_replace("%00", "\\%00", $value); // null
$value = str_replace("%09", "\\%09", $value); // \t
$value = str_replace("%0a", "\\%0a", $value); // \n
$value = str_replace("%0d", "\\%0d", $value); // \r
$value = str_replace("%020", "\\%20", $value); // space
*/
$value = str_replace("\0", "\\0", $value);
$value = str_replace("\x08", "\\b", $value);
$value = str_replace("\x09", "\\t", $value);
$value = str_replace("\x1a", "\\Z", $value);
$value = str_replace("\n", "\\n", $value);
$value = str_replace("\r", "\\r", $value);
$value = str_replace("\"", "\\\"", $value);
$value = str_replace("'", "\\'", $value);
$value = str_replace("\\", "\\\\", $value);
$value = str_replace("|", "\\|", $value);
$value = str_replace("&", "\\&", $value);
$value = str_replace("#", "\\#", $value);
$value = str_replace("+", "\\+", $value);
$value = str_replace(";", "\\;", $value);
$value = str_replace("<", "\\<", $value);
$value = str_replace(">", "\\>", $value);
$value = preg_replace("/sleep\(/i", "sleep\\(", $value);
$value = preg_replace("/benchmark\(/i", "benchmark\\(", $value);
$value = preg_replace("/analyse\(/i", "analyse\\(", $value);
$value = preg_replace("/concat\(/i", "concat\\(", $value);
$value = preg_replace("/union\(/i", "union\\(", $value);
$value = preg_replace("/select\(/i", "select\\(", $value);
$value = preg_replace("/update\(/i", "update\\(", $value);
$value = preg_replace("/delete\(/i", "delete\\(", $value);
$value = preg_replace("/insert\(/i", "insert\\(", $value);
$value = preg_replace("/create\(/i", "create\\(", $value);
$value = preg_replace("/drop\(/i", "drop\\(", $value);
$value = preg_replace("/alter\(/i", "alter\\(", $value);
$value = preg_replace("/rename\(/i", "rename\\(", $value);
$value = preg_replace("/truncate\(/i", "truncate\\(", $value);
$value = preg_replace("/replace\(/i", "replace\\(", $value);
$value = preg_replace("/load_file\(/i", "load_file\\(", $value);
$value = preg_replace("/outfile\(/i", "outfile\\(", $value);
$value = preg_replace("/dumpfile\(/i", "dumpfile\\(", $value);
$value = preg_replace("/substring\(|substr\(/i", "substr\\(", $value);
$value = preg_replace("/ascii\(/i", "ascii\\(", $value);
$value = preg_replace("/mid\(/i", "mid\\(", $value);
$value = preg_replace("/char\(/i", "char\\(", $value);
$value = preg_replace("/hex\(/i", "hex\\(", $value);
$value = preg_replace("/bin\(/i", "bin\\(", $value);
$value = preg_replace("/version\(/i", "version\\(", $value);
$value = preg_replace("/database\(/i", "database\\(", $value);
$value = preg_replace("/user\(/i", "user\\(", $value);
$value = preg_replace("/schema\(/i", "schema\\(", $value);
$value = preg_replace("/table\(/i", "table\\(", $value);
$value = preg_replace("/column\(/i", "column\\(", $value);
$value = preg_replace("/procedure\(/i", "procedure\\(", $value);
$value = preg_replace("/function\(/i", "function\\(", $value);
$value = preg_replace("/where\(/i", "where\\(", $value);
$value = preg_replace("/having\(/i", "having\\(", $value);
$value = preg_replace("/order\(/i", "order\\(", $value);
$value = preg_replace("/group\(/i", "group\\(", $value);
$value = preg_replace("/by\(/i", "by\\(", $value);
$value = preg_replace("/desc\(/i", "desc\\(", $value);
$value = preg_replace("/asc\(/i", "asc\\(", $value);
$value = preg_replace("/reverse\(/i", "reverse\\(", $value);
return $value;
}
JavaScript
๋ณต์ฌ