如何阻止SQL注入漏洞? [英] How to stop SQL injection vulnerability?
问题描述
想知道这对 SQL 注入有多脆弱.我听说使用准备好的 sql 语句可以绕过这个漏洞，但我也听说使用双引号代替单引号也可以防止 SQL 注入.我不是安全专家，也不擅长使用 sqlite.另外，我需要在其他地方初始化数据库，并且可能最终使用准备好的语句而不是 sprintf，但我只是不确定如何做其中任何一件事情.非常感谢任何帮助！谢谢！
Was wondering how vulnerable this was to SQL injection. I heard that using prepared sql statements can circumvent this vulnerability, but I also heard that using double quotes instead of single quotes can prevent SQL injection as well. I am not a security expert, and i'm not great with sqlite either. Also, I need to initalize the database elsewhere, and probably end up using prepared statements instead of sprintf, but i'm just not exactly sure how to do either one of those things. Any help is greatly appreciated! Thank you!
bool sql_console_msgs = false;
void QServ::savestats(clientinfo *ci)
{
if(enable_sqlite_db) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
const char *sql;
bool name_match;
const char* player_database_names;
char *p_name = ci->name;
char *p_ip = ci->ip;
rc = sqlite3_open("playerinfo.db", &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
exit(0);
}else{
if(sql_console_msgs) fprintf(stdout, "Opened database successfully\n");
}
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL Database Error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
sqlite3_stmt *stmt;
defformatstring(sqlstrprep)("SELECT NAME FROM PLAYERINFO");
rc = sqlite3_prepare_v2(db, sqlstrprep, -1, &stmt, NULL);
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
player_database_names = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
if(!strcmp(player_database_names, p_name)) name_match = true;
else name_match = false;
}
}
sql = "CREATE TABLE IF NOT EXISTS PLAYERINFO(" \
"NAME TEXT NOT NULL," \
"FRAGS INT NOT NULL," \
"DEATHS INT NOT NULL," \
"FLAGS INT NOT NULL," \
"PASSES INT NOT NULL," \
"IP TEXT NOT NULL," \
"ACCURACY DECIMAL(4, 2) NOT NULL," \
"KPD DECIMAL(4, 2) NOT NULL);";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQLITE3 ERROR @ CREATE TABLE IF NOT EXISTS: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
if(sql_console_msgs) {
if(!name_match) fprintf(stdout, "No previous record found under that name\n");
else fprintf(stdout, "Found name and IP already, updating record instead\n");
}
}
char sqlINSERT[256];
char sqlUPDATE[1000];
int p_frags = ci->state.frags;
int p_deaths = ci->state.deaths;
int p_flags = ci->state.flags;
int p_passes = ci->state.passes;
int p_acc = (ci->state.damage*100)/max(ci->state.shotdamage, 1);
int p_kpd = (ci->state.frags)/max(ci->state.deaths, 1);
//name and ip are different
if(!name_match) {
sprintf(sqlINSERT, "INSERT INTO PLAYERINFO( NAME,FRAGS,DEATHS,FLAGS,PASSES,IP,ACCURACY,KPD ) VALUES ('%s', %d, %d, %d, %d, '%s', %d, %d)",p_name,p_frags,p_deaths,p_flags,p_passes,p_ip,p_acc,p_kpd);
rc = sqlite3_exec(db, sqlINSERT, callback, 0, &zErrMsg);
}
//client name matches db record, update db if new info is > than db info
else if(name_match) {
sprintf(sqlUPDATE,
"UPDATE PLAYERINFO SET FRAGS = %d+(SELECT FRAGS FROM PLAYERINFO) WHERE NAME = '%s';" \
"UPDATE PLAYERINFO SET DEATHS = %d+(SELECT DEATHS FROM PLAYERINFO) WHERE NAME = '%s';" \
"UPDATE PLAYERINFO SET FLAGS = %d+(SELECT FLAGS FROM PLAYERINFO) WHERE NAME = '%s';" \
"UPDATE PLAYERINFO SET PASSES = %d+(SELECT PASSES FROM PLAYERINFO) WHERE NAME = '%s';" \
"UPDATE PLAYERINFO SET ACCURACY = %d+(SELECT PASSES FROM PLAYERINFO) WHERE NAME = '%s';" \
"UPDATE PLAYERINFO SET KPD = %d+(SELECT PASSES FROM PLAYERINFO) WHERE NAME = '%s';",
ci->state.frags, ci->name, ci->state.deaths, ci->name, ci->state.flags, ci->name, ci->state.passes, ci->name, p_acc, ci->name, p_kpd, ci->name);
rc = sqlite3_exec(db, sqlUPDATE, callback, 0, &zErrMsg);
}
if( rc != SQLITE_OK ){
fprintf(stderr, "SQLITE3 ERROR @ INSERT & UPDATE: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
if(sql_console_msgs) fprintf(stdout, "Playerinfo modified\n");
}
sqlite3_close(db);
}
}
void QServ::getstats(clientinfo *ci)
{
if(enable_sqlite_db) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
const char* data = "Callback function called";
rc = sqlite3_open("playerinfo.db", &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
exit(0);
}
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL Database Error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
sqlite3_stmt *stmt;
defformatstring(sqlstrprep)("SELECT NAME,FRAGS,ACCURACY,KPD FROM PLAYERINFO WHERE NAME == '%s';", ci->name);
rc = sqlite3_prepare_v2(db, sqlstrprep, -1, &stmt, NULL);
bool necho = false;
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
const char* name = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
const char* allfrags = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
const char* avgacc = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
const char* avgkpd = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
if(!necho) {
if(avgacc == NULL) out(ECHO_SERV, "Name: \f0%s\f7, Total Frags: \f3%s\f7, Average KPD: \f6%s", name, allfrags, avgkpd);
else if(avgkpd == NULL) out(ECHO_SERV, "Name: \f0%s\f7, Total Frags: \f3%s\f7, Average Accuracy: \f2%s%%", name, allfrags, avgacc);
else out(ECHO_SERV, "Name: \f0%s\f7, Total Frags: \f3%s\f7, Average Accuracy: \f2%s%%\f7, Average KPD: \f6%s", name,allfrags,avgacc,avgkpd);
necho = true;
}
}
}
sqlite3_close(db);
}
}
void QServ::getnames(clientinfo *ci) {
if(enable_sqlite_db) {
sqlite3_stmt *stmt3;
sqlite3 *db;
int rc;
rc = sqlite3_open("playerinfo.db", &db);
defformatstring(sqlstrprep3)("SELECT group_concat(NAME, ', ') FROM PLAYERINFO WHERE IP == '%s';", ci->ip);
rc = sqlite3_prepare_v2(db, sqlstrprep3, -1, &stmt3, NULL);
while ((rc = sqlite3_step(stmt3)) == SQLITE_ROW) {
std::string names(reinterpret_cast<const char*>(sqlite3_column_text(stmt3, 0)));
defformatstring(nmsg)("Names from IP \f2%s\f7: %s", ci->ip, names.c_str());
out(ECHO_SERV, nmsg);
}
sqlite3_close(db);
}
}
推荐答案
手动格式化 SQL 语句时(当涉及输入参数时，您真的不应该这样做！)，仅将参数值括在引号中是不够的.您还需要转义参数数据中的保留字符，否则您仍然容易受到注入攻击.攻击者可以简单地在数据中放置一个匹配的引号，关闭您的开场引号，然后其余的参数数据可以包含恶意指令.
When formatting an SQL statement by hand (which you really SHOULD NOT do when input parameters are involved!), it is not enough to merely wrap parameter values in quotes. You need to also escape reserved characters inside of the parameter data, otherwise you are still susceptible to injection attacks. The attacker could simply place a matching quote inside the data, closing off your opening quote, and then the rest of the parameter data can contain malicious instructions.
例如:
const char *p_name = "'); DROP TABLE MyTable; --";
sprintf(sql, "INSERT INTO MyTable(NAME) VALUES ('%s')", p_name);
或者:
const char *p_name = "\"); DROP TABLE MyTable; --";
sprintf(sql, "INSERT INTO MyTable(NAME) VALUES (\"%s\")", p_name);
这些将创建以下 SQL 语句:
These would create the following SQL statements:
INSERT INTO MyTable(NAME) VALUES (''); DELETE TABLE MyTable; --')
INSERT INTO MyTable(NAME) VALUES (""); DELETE TABLE MyTable; --")
说再见"执行 SQL 时到您的表！(假设执行 SQL 的用户具有对表的 DELETE 访问权限 - 这是它自己的另一个安全问题).
Say "bye bye" to your table when the SQL is executed! (assuming the user executing the SQL has DELETE access to the table - that is a whole other security concern of its own).
在这种情况下，您需要将参数数据中的任何单引号字符加倍，或斜线转义任何双引号字符，例如:
In this case, you would need to double up any single-quote characters, or slash-escape any double-quote characters, that are in the parameter data, eg:
const char *p_name = "'); DROP TABLE MyTable; --";
char *p_escaped_name = sqlEscape(p_name); // <-- you have to implement this yourself!
sprintf(sql, "INSERT INTO MyTable(NAME) VALUES ('%s')", p_escaped_name);
// or:
// sprintf(sql, "INSERT INTO MyTable(NAME) VALUES (\"%s\")", p_escaped_name);
free(p_escaped_name);
因此，生成的 SQL 语句将如下所示:
Thus, the resulting SQL statements would look like these instead:
INSERT INTO MyTable(NAME) VALUES ('''); DELETE TABLE MyTable; --')
INSERT INTO MyTable(NAME) VALUES ("\"); DELETE TABLE MyTable; --")
因此，插入表中的名称将是 ');删除表 MyTable；-- (或 ");删除表 MyTable；--).不漂亮，但表格会被保存.
Thus, the name inserted into the table would be '); DELETE TABLE MyTable; -- (or "); DELETE TABLE MyTable; --). Not pretty, but the table would be saved.
某些数据库框架提供了为您执行此转义的函数，但我在 sqlite 中没有看到，因此您必须在自己的代码中手动实现它，例如:
Some DB frameworks offer functions to do this escaping for you, but I don't see one in sqlite, so you will have to implement it manually in your own code, eg:
char* sqlEscape(const char *str)
{
int len = strlen(str);
int newlen = len;
for (int i = 0; i < len; ++i) {
switch (str[i]) {
case '\'':
case '"':
++newlen;
break;
}
}
if (newlen == len)
return strdup(str);
char *newstr = (char*) malloc(newlen + 1);
if (!newstr)
return NULL;
newlen = 0;
for (int i = 0; i < len; ++i) {
switch (str[i]) {
case '\'':
newstr[newlen++] = '\'';
break;
case '"':
newstr[newlen++] = '\\';
break;
}
newstr[newlen++] = str[i];
}
newstr[newlen] = '\0';
return newstr;
}
准备好的语句通过让数据库引擎在执行准备好的语句时为您处理这些详细信息，避免了手动执行此转义的需要.
A prepared statement avoids the need to do this escaping manually, by letting the DB engine handle these details for you when executing the prepared statement.
此外，您的代码对 sprintf() 的使用容易受到缓冲区溢出的影响，甚至更糟，因为精心设计的缓冲区溢出可以让攻击者在您的应用程序中执行任意机器代码，而不仅仅是在数据库中.改用 snprintf() 来避免这种情况.
Also, your code's use of sprintf() is susceptible to buffer overflows, which is even worse, because a carefully crafted buffer overflow can let an attacker execute arbitrary machine code inside your app, not just in the database. Use snprintf() instead to avoid that.
这篇关于如何阻止SQL注入漏洞?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
