文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >如何阻止SQL注入漏洞？

问如何阻止SQL注入漏洞？
EN

Stack Overflow用户
提问于 2020-12-10 00:40:34
回答 2查看 423关注 0票数 0

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

代码语言：javascript
复制
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);
    }
}
c++
sqlite
sql-injection
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2020-12-10 01:07:59

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

例如：

代码语言：javascript
复制
const char *p_name = "'); DROP TABLE MyTable; --";
sprintf(sql, "INSERT INTO MyTable(NAME) VALUES ('%s')", p_name);

或者：

代码语言：javascript
复制
const char *p_name = "\"); DROP TABLE MyTable; --";
sprintf(sql, "INSERT INTO MyTable(NAME) VALUES (\"%s\")", p_name);

这些语句将创建以下SQL语句：

代码语言：javascript
复制
INSERT INTO MyTable(NAME) VALUES (''); DELETE TABLE MyTable; --')
代码语言：javascript
复制
INSERT INTO MyTable(NAME) VALUES (""); DELETE TABLE MyTable; --")

在执行SQL时，请对表说“再见”！(假设执行SQL的用户对表具有DELETE访问权限--这是它自己的另一个安全问题)。

在这种情况下，需要加倍使用参数数据中的任何单引号字符或斜杠转义任何双引号字符，例如：

代码语言：javascript
复制
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语句应该如下所示：

代码语言：javascript
复制
INSERT INTO MyTable(NAME) VALUES ('''); DELETE TABLE MyTable; --')
代码语言：javascript
复制
INSERT INTO MyTable(NAME) VALUES ("\"); DELETE TABLE MyTable; --")

因此，插入到表中的名称将是'); DELETE TABLE MyTable; -- (或"); DELETE TABLE MyTable; --)。不漂亮，但桌子会被保存下来。

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

代码语言：javascript
复制
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;
}

准备好的语句避免了手动转义的需要，方法是让DB引擎在执行准备语句时为您处理这些细节。

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

票数 1
EN

Stack Overflow用户

发布于 2020-12-10 12:37:51

下面是由@Remy提供的建议的更新代码

代码语言：javascript
复制
//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] = '\0';
    
    return newstr;
}

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
    int i;
    for(i=0; i<argc; i++){
        printf("%s = %s\n", 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: %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) {
            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: %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);
    }
}
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/65226950

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档