在C程序中从PostgreSQL检索数据

Question

在我的C程序中使用libpq db引擎在postgresql db中检索数据时遇到问题。存储数据后，我可以使用我的终端验证其完整性，因此它保留在数据库中，但是当我尝试访问它时，我得到了一个 segv(由于空引用/ptr(。以下是使用的相关例程/功能。

创建表：

int createTable() {
    const char *conninfo = "user=tmp password=pass dbname=testdb hostaddr=127.0.0.1 port=5432 sslmode=require";
    PGconn *conn = PQconnectdb(conninfo);   /* connect to db */
    PGresult *res;
    FILE data;
    int nFields;
    int i, j;
    // Make a connection to the database
    conn = PQconnectdb(conninfo);
    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        return -1;
    }
    printDBInfo(conn); // DEBUG
    /* drop table if exists */
    res = PQexec(conn, "DROP TABLE IF EXISTS Users");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        failInt(conn, res);
    }
    PQclear(res);
    /* create table */
    res = PQexec(conn, "CREATE TABLE Users(username VARCHAR(20) PRIMARY KEY," 
        "password VARCHAR(45))");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        failInt(conn, res);
    }
    PQclear(res);
    /* add some users */
    res = PQexec(conn, "INSERT INTO Users (username,password) VALUES ('foo','bar')");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
        failInt(conn, res);
    }
    PQclear(res);
    res = PQexec(conn, "INSERT INTO Users (username,password) VALUES ('foofoo','extrabar')");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
        failInt(conn, res);
    }
    PQclear(res);
    res = PQexec(conn, "INSERT INTO Users (username,password) VALUES ('TheFooestF00','H1gh3stBar')");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
        failInt(conn, res);
    }
    PQclear(res);
//    res = PQexec(conn, "COMMIT");
//    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
//        printf("COMMIT command failedn");
//        failInt(res, conn);
//    }
//    PQclear(res);
    PQfinish(conn); /* close the connection */
    return 0;
}

身份验证(这是我获得某种类型的空引用的地方(

/* TODO: whitelisting / parsing / verifying user and pass */
int authenticateUser(const char *user, const char *pass) {
    const char *statement = "SELECT user FROM Users WHERE user=";
    size_t query_size = strlen(statement) + strlen(user) + 1;
    char *query = malloc(query_size);
    memset(query, '', query_size);
    PGconn *conn = PQconnectdb("user=tmp password=pass dbname=testdb hostaddr=127.0.0.1 port=5432 sslmode=require");
    if (PQstatus(conn) == CONNECTION_BAD) {
        fprintf(stderr, "Connection to database failed: %sn", PQerrorMessage(conn));
        PQfinish(conn);
        exit(1);
    }
    strcat(query, statement);
    strcat(query, user);
    strcat(query, "");
    printf("query: %sn",query);
    PGresult *res = PQexec(conn, query);
    printf("num of tuples: %in", PQntuples(res));
    printf("num of columns: %in", PQnfields(res));
//    PQprintTuples(res, STDOUT_FILENO, )
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        printf("No data retrievedn");
        failInt(conn, res);
    }
    const char *pass_check = PQgetvalue(res, 0, 1);
    if (strcmp(user, pass_check) == 0) {
        success(conn, res);
    }
    PQclear(res);
    PQfinish(conn);
    return -1;
}

我知道它需要一些输入验证，这是列表中的下一个:)

编辑：

GDB 输出

Program received signal SIGSEGV, Segmentation fault.
0x00007ffff787c9de in ?? () from /lib/x86_64-linux-gnu/libc.so.6
(gdb) backtrace
#0  0x00007ffff787c9de in ?? () from /lib/x86_64-linux-gnu/libc.so.6
#1  0x0000000000402520 in authenticateUser (user=0x402b05 "foo", pass=0x402b1d "bar") at /pwdmanlib/src/db/database.h:272
#2  0x000000000040266b in main () at /pwdmanlib/src/test/db_test.c:51
(gdb) frame 1
#1  0x0000000000402520 in authenticateUser (user=0x402b05 "foo", pass=0x402b1d "bar") at /pwdmanlib/src/db/database.h:272
272     if (strcmp(user, pass_check) == 0) {
(gdb) next
Cannot find bounds of current function

这一行：const char *pass_check = PQgetvalue(res, 0, 1);返回一个空 ptr，我不知道为什么，因为我在此之前使用相同的参数调用相同的 func 并且它可以工作。

Answer 1

为了回答我问的原始问题，问题是查询的语法不正确，我不得不在 0,0 上 PQgetvalue，因为它返回我在查询中要求的 (1( 值而不是元组(就像我最初认为的那样(。下面为偶然发现此内容的任何其他人提供了完整的实现。快乐的黑客:)

int authenticateUser(const char *user, const char *pass) {
    const char *statement = "SELECT password FROM Users WHERE username='";
    size_t query_size = strlen(statement) + strlen(user) + 3;
    char *query = malloc(query_size);
    memset(query, '', query_size);
    PGconn *conn = PQconnectdb("user=tmp password=pass dbname=testdb hostaddr=127.0.0.1 port=5432 sslmode=require");
    if (PQstatus(conn) == CONNECTION_BAD) {
        fprintf(stderr, "Connection to database failed: %sn", PQerrorMessage(conn));
        PQfinish(conn);
        return -1;
    }
    strcat(query, statement);
    strcat(query, user);
    strcat(query, "';");
    printf("query: %sn",query);
    PGresult *res = PQexec(conn, query);
    printf("num of tuples: %in", PQntuples(res));
    printf("num of columns: %in", PQnfields(res));
//    PQprintTuples(res, STDOUT_FILENO, )
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        printf("No data retrievedn");
        return failInt(conn, res);
    }
    char *pass_check = PQgetvalue(res, 0, 0);
    if (strcmp(pass, pass_check) == 0) {
        return success(conn, res);
    }
    PQclear(res);
    PQfinish(conn);
    return -1;
}