传递C变量到SQL命令 [英] Passing C variables into SQL command
问题描述
我在用的libpq和工作PostgreSQL数据库上的新手。
I am a newbie in using libpq and work on a postgresql database.
所以，到目前为止，我可以插入/更新/ etc PostgreSQL数据库使用C程序，只要我给了引号内的实际值。
So, far I can insert/update/etc a postgresql database using C program, provided I give the actual values inside the quotes.
我想知道如何传递一个字符串/整型变量在命令??
I want to know how to pass a string/integer variable in the command??
例如。下面code添加了一个名为注释，载TRUE默认值到现有的表中人的专栏。我需要假以更新评论的值，其中的ID = 2
E.g. The following code adds a column called "comment" containing "TRUE" default value into an existing table "people". I need to update the value of the "comment" by "FALSE" where the id=2.
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
#include <string.h>
void exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int main()
{
PGconn *conn;
PGresult *res;
int nFields;
int row_count=0,col_count=0;
int row=0;
int col=0;
conn = PQconnectdb("dbname=test host=localhost user=postgres password=xxx");
if(PQstatus(conn) == CONNECTION_BAD)
{
fprintf(stderr, "Connection to database \"%s\" failed.\n", PQerrorMessage(conn));
fprintf(stderr, "%s", PQerrorMessage(conn));
exit_nicely(conn);
}
res = PQexec(conn, "ALTER TABLE people ADD comment VARCHAR(50) DEFAULT 'TRUE'");
if((!res) || PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Adding col to table (ALTER) Failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
res = PQexec(conn, "SELECT * FROM people");
if((!res) || (PQresultStatus(res) != PGRES_TUPLES_OK))
{
fprintf(stderr, "SELECT command did not return tuples properly\n");
PQclear(res);
}
int query1 = 2;
res = PQexec(conn,"UPDATE people SET comment='FALSE' WHERE id =\'query1\'");
if((!res) || PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "Insertion Failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
else
printf("Successfully inserted value in Table..... \n");
res = PQexec(conn, "SELECT * FROM people");
if((!res) || (PQresultStatus(res) != PGRES_TUPLES_OK))
{
fprintf(stderr, "SELECT command did not return tuples properly\n");
PQclear(res);
}
puts("==========================");
for(row=0;row<PQntuples(res);row++)
{
for(col=0;col<PQnfields(res);col++)
{
printf("%s\t", PQgetvalue(res, row, col));
}
puts("");
}
PQclear(res);
PQfinish(conn);
return 0;
}
我想下面的输出：
I want the following output:
id | firstname | lastname | comment
1 | Fred | Flintstone | 5055551234 | TRUE
2 | Wilma | Flintstone | 5055551234 | FALSE
5 | XXX | YYY | 7633839276 | TRUE
3 | Barny | Rubble | 5055550000 | TRUE
不过，我收到以下错误：
However, I am getting the following error:
Insertion Failed: ERROR: invalid input syntax for integer: "query1"
LINE 1: UPDATE people SET comment='FALSE' WHERE id ='query1'
请帮我一些建议。
推荐答案
有两种方式来处理这个问题。第一是prepare与插在它的值的字符串。二是使用查询参数，您可以分别替换值。
There are two ways to handle this. The first is to prepare the string with the values inserted in it. The second is to use query parameters for which you can substitute values separately.
对于第一种方法，你可以用一个函数，如的snprintf 以prepare你将发送到服务器的命令。例如：
For the first method, you can use a function such as snprintf to prepare the command you will send to the server. For example:
char buffer[512];
int num=snprintf(buffer, sizeof(buffer),
"SELECT name FROM MYTABLE WHERE id=%d", id);
if (num>sizeof(buffer)) {
/* error: buffer was too small */
}
在此缓冲器将包含SQL查询包括变量ID的实际值
After this buffer will contain the SQL query including the actual value of the variable id.
请注意需要从snprintf的检查返回值，看是否缓冲区溢出。
Note the need to check the return value from snprintf to see if the buffer overflowed.
另外请注意，当一个字符串被放置在命令中，您需要确保该字符串不包含任何引号或其他特殊字符。如果字符串来自你的程序，例如之外。从用户的输入，则无法正确引用它留下了一个大洞，通过它有人可以注入一些恶意的SQL。 libpq的提供 PQescapeLiteral 功能这一点。
Also note that when a string is being placed in the command, you need to ensure that the string does not contain any quotes or other special characters. If the string comes from outside your program, eg. From user input, then failing to quote it properly leaves a big hole through which someone could inject some malicious SQL. libpq provides the PQescapeLiteral function for this.
另一种方法，这是在大多数情况下，preferable，是到SQL命令和参数分别传递到服务器。例如，您可以用做 PQexecParams 的libpq functoin。您的SQL字符串应该是这样的：
The other method, which is preferable in most cases, is to pass the SQL command and the parameters to the server separately. For example, you can do this using PQexecParams libpq functoin. Your SQL string would look like this:
PGresult r = PQexecParams(conn, /* Connection to database */
"SELECT name FROM mytable WHERE id=$1",
1, /* Number of parameters */
NULL, /* NULL means server should figure out the parameter types */
params, /* Pointer to array of strings containing parameters */
NULL, /* Not needed unless binary format used */
NULL, /* Not needed unless binary format used */
0 /* Result to come back in text format */
);
这个功能允许你提供的参数和/或获取文本或者二进制格式的结果。为简单起见我上面的例子假设文本格式为。
This function allows you to provide parameters and/or obtain results in either text or binary format. For simplicity my example above assumes text format for both.
在这方面的一个变化是使用prepared语句。在这种情况下，你让两个独立的呼叫LIBPQ：
A variation on this is to use prepared statements. In this case, you make two separate calls to libpq:
-
通话PQ prepare，对此你通过SQL语句的参数值$ 1，$ 2等，按照我上面的例子。这将返回一个语句句柄。
Call PQprepare, to which you pass your SQL statement with parameter values $1, $2, etc, as per my example above. This will return a statement handle.
通话PQexec的prepared，对此您通过语句句柄，并参数本身，以类似的方式指定PQexecParams。
Call PQexecPrepared, to which you pass the statement handle and also the parameters themselves, specified in a similar fashion to PQexecParams.
使用两个步骤是这样的优点是，可以一次prepare的说明书，并执行它的许多倍，这减少了与解析它和规划该查询相关联的服务器的开销的量。
The advantage of using two steps like this is that you can prepare the statment once, and execute it many times, which reduces the amount of server overhead associated with parsing it and planning the query.
这篇关于传递C变量到SQL命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
