Can we use a dynamic variable in an SQL select query in a C program?

Question

I actually want to execute an SQL query in a C program, with the variables in a SELECT query to be the value stored in a string variable.

For example:

void fetch_data(char var[])
{
    char COL1[]=var, COL2[]="Address", COL3[]="Name";
    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL4='some value';
}

Here as you can see I want want my code to be flexible so that I can have different column names depending on the variable var which is the parameter of the fetch_data function.

Please tell me if this is possible in C.

I have thought of another method if the above is not possible: can we store the whole SQL statement in a string and execute it, so that I can modify this string whenever I want according to the parameter's value that I get in the function fetch_data()?

The code below will make my point more clear that what I want:

void fetch_data(char var[])
{
    char COL1[]="Name", COL2[]="Address", COL3[]=var;
    char qry1[]="SELECT ", qry2[]=var, qry3=" COL2, COL3 FROM TABLE WHERE COL4='some value';";
    char str[]=strcat(qry1,qry2);
    char query[]=strcat(str,qry3);
    //now query will be having "select (value of var), COL2, COL3 FROM TABLE WHERE COL4='some value';
}

Now in the above code, can I execute the query that is stored in the string query?

Please let me know if any of the 2 methods can work or if it can be achieved by any other method in 'C'.

How to do this depends on your database driver. But generally, you shouldn't modify the query string, it's really insecure. You should use a function/set of functions that allow you to put placeholders where the varying parts will be, and then pass the actual values as parameters. — ninjalj
– ninjalj, Commented Sep 8, 2012 at 20:11

pb2q · Accepted Answer · 2012-09-08 17:41:08Z

2

The second method will certainly work, you'll just need to be careful with your string manipulation.

In fact you could simplify it using snprintf:

snprintf(queryStr, MAX_QUERY_LENGTH,
         "SELECT %s, COL2, COL3 FROM TABLE WHERE COL4='some value';", var);

If the query needs to be more dynamic, i.e. 'some value' also needs to come from a variable, you can add additional format specifiers:

snprintf(queryStr, MAX_QUERY_LENGTH,
         "SELECT %s, COL2, COL3 FROM TABLE WHERE COL4='%s';", var, someValue);

Once you've prepared such a query, just how you use it depends upon your toolchain. Are you using ODBC? If so, then you can use ODBC calls to handle your queries, and ODBC drivers to manage the connection to your database. Otherwise, which database are you using, and what API is provided to you? For instance, MySQL provides a pretty large C API, oracle a different one, and SQL Server another.

As for the first option, you'll need to make use of a SQL preprocessor to use something like raw SQL statements in your C code, and the syntax will depend upon which tool that you use.

I should also add that you can't copy non-literal C strings using a statement like: char COL1[]=var;. Instead you need to use a string library method like strncpy.

edited Sep 8, 2012 at 17:41

answered Sep 7, 2012 at 14:43

pb2q

59.9k19 gold badges150 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MK Singh Over a year ago

Thanks. Please also tell me how to execute a query which is stored in a string in a C program.

Collectives™ on Stack Overflow

Can we use a dynamic variable in an SQL select query in a C program?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related