小贝子编程

如何阻止SQL注入漏洞



想知道这对SQL注入有多脆弱。我听说使用准备好的sql语句可以绕过这个漏洞，但我也听说使用双引号而不是单引号也可以防止sql注入。我不是一个安全专家，我也不擅长sqlite。此外，我需要在其他地方初始化数据库，最终可能会使用准备好的语句而不是sprintf，但我只是不确定如何做这两件事。非常感谢您的帮助！非常感谢。

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: %sn", sqlite3_errmsg(db));
exit(0);
}else{
if(sql_console_msgs) fprintf(stdout, "Opened database successfullyn");
}
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL Database Error: %sn", 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: %sn", zErrMsg);
sqlite3_free(zErrMsg);
}else{
if(sql_console_msgs) {
if(!name_match) fprintf(stdout, "No previous record found under that namen");
else fprintf(stdout, "Found name and IP already, updating record insteadn");
}
}

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: %sn", zErrMsg);
sqlite3_free(zErrMsg);
}else{
if(sql_console_msgs) fprintf(stdout, "Playerinfo modifiedn");
}
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: %sn", sqlite3_errmsg(db));
exit(0);
}

if( rc != SQLITE_OK ){
fprintf(stderr, "SQL Database Error: %sn", 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%sf7, Total Frags: f3%sf7, Average KPD: f6%s", name, allfrags, avgkpd);
else if(avgkpd == NULL) out(ECHO_SERV, "Name: f0%sf7, Total Frags: f3%sf7, Average Accuracy: f2%s%%", name, allfrags, avgacc);
else out(ECHO_SERV, "Name: f0%sf7, Total Frags: f3%sf7, 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%sf7: %s", ci->ip, names.c_str());
out(ECHO_SERV, nmsg);
}
sqlite3_close(db);
}
}

手动格式化SQL语句时(当涉及输入参数时，确实不应该这样做！(，仅仅用引号括住参数值是不够的。您还需要转义参数数据中的保留字符，否则您仍然容易受到注入攻击。攻击者可以简单地在数据中放置一个匹配的引号，关闭开头的引号，然后参数数据的其余部分可能包含恶意指令。

例如：

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语句：

INSERT INTO MyTable(NAME) VALUES (''); DELETE TABLE MyTable; --')

INSERT INTO MyTable(NAME) VALUES (""); DELETE TABLE MyTable; --")

说"再见"；在执行SQL时将其添加到您的表中！(假设执行SQL的用户具有对表的DELETE访问权限——这是它自己的另一个安全问题(。

在这种情况下，您需要将参数数据中的任何单引号字符加倍，或对任何双引号字符进行斜线转义，例如：

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语句将改为如下所示：

INSERT INTO MyTable(NAME) VALUES ('''); DELETE TABLE MyTable; --')

INSERT INTO MyTable(NAME) VALUES (""); DELETE TABLE MyTable; --")

因此，插入到表中的名称将是'); DELETE TABLE MyTable; --(或"); DELETE TABLE MyTable; --(。虽然不漂亮，但这张桌子会保住的。

一些DB框架为您提供了这样的转义功能，但我在sqlite中没有看到，所以您必须在自己的代码中手动实现它，例如：

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] = '';
return newstr;
}

通过让DB引擎在执行prepared语句时为您处理这些细节，prepared声明避免了手动进行转义的需要。

此外，您的代码对sprintf()的使用容易受到缓冲区溢出的影响，这甚至更糟，因为精心设计的缓冲区溢出可以让攻击者在您的应用程序中执行任意机器代码，而不仅仅是在数据库中。使用snprintf()来避免这种情况。

以下是更新后的代码和@Remy Lebeau 提供的建议

//no naughty sql injection
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] = '';

return newstr;
}
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i<argc; i++){
printf("%s = %sn", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("n");
return 0;
}
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: %sn", sqlite3_errmsg(db));
exit(0);
}else{
if(sql_console_msgs) fprintf(stdout, "Opened database successfullyn");
}
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL Database Error: %sn", 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: %sn", zErrMsg);
sqlite3_free(zErrMsg);
}else{
if(sql_console_msgs) {
if(!name_match) fprintf(stdout, "No previous record found under that namen");
else fprintf(stdout, "Found name and IP already, updating record insteadn");
}
}

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) {
snprintf(sqlINSERT, sizeof(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);
sqlEscape(sqlINSERT);
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)  {
snprintf(sqlUPDATE, sizeof(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);
sqlEscape(sqlINSERT);
rc = sqlite3_exec(db, sqlUPDATE, callback, 0, &zErrMsg);
}
if( rc != SQLITE_OK ){
fprintf(stderr, "SQLITE3 ERROR @ INSERT & UPDATE: %sn", zErrMsg);
sqlite3_free(zErrMsg);
}else{
if(sql_console_msgs) fprintf(stdout, "Playerinfo modifiedn");
}
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: %sn", sqlite3_errmsg(db));
exit(0);
}

if( rc != SQLITE_OK ){
fprintf(stderr, "SQL Database Error: %sn", 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%sf7, Total Frags: f3%sf7, Average KPD: f6%s", name, allfrags, avgkpd);
else if(avgkpd == NULL) out(ECHO_SERV, "Name: f0%sf7, Total Frags: f3%sf7, Average Accuracy: f2%s%%", name, allfrags, avgacc);
else out(ECHO_SERV, "Name: f0%sf7, Total Frags: f3%sf7, Average Accuracy: f2%s%%f7, Average KPD: f6%s", name,allfrags,avgacc,avgkpd);
necho = true;
}
}
}
sqlite3_close(db);
}
}

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium