mysql and c -- use variable in query

Question

I'm writing a c program with an mysql database connection and I'm having difficulties creating my mysql queries...

i want to you a variable integer in my mysql query, but i can't seem to get i right...

my current query looks like this...

mysql_query(conn, "INSERT INTO markerherkenning (MARKER_ID, DATETIME) values(1, CURRENT_TIME())");

my marker_ID value should be a variable value, so i can reduce my code...

i have used this guide to get me going... (http://zetcode.com/tutorials/mysqlcapitutorial)

thanks for helping

can you build it into the string? IE: "INSERT INTO markerherkenning (MARKER_ID, DATETIME) values(" + variable + "CURRENT_TIME())" forget how to do this in c exactly, but that kind of thing — Chris
– Chris, Commented May 9, 2011 at 18:05
@Chris: please do not suggest that people open themselves up to SQL injection. — Wooble
– Wooble, Commented May 9, 2011 at 18:07
@Chris: building SQL statments by concatenating strings blindly is a recipe for disaster. — Wooble
– Wooble, Commented May 9, 2011 at 18:17
@Wooble: not sure how converting an integer value to a string internally opens you up to an injection attack. — John Bode
– John Bode, Commented May 9, 2011 at 18:18
@jordi: are you using mysql++ or C? mysql++ is a library for C++. — Wooble
– Wooble, Commented May 9, 2011 at 18:18

S.J. · Accepted Answer · 2011-05-09 18:19:59Z

2

Something like this:


sprintf(request, "INSERT INTO markerherkenning (%d, DATETIME) values(1, CURRENT_TIME())", marker_id);

At first, you make a string with your request with sprintf (or snprintf), and then use it for sql query.

answered May 9, 2011 at 18:19

S.J.

1,2332 gold badges12 silver badges15 bronze badges

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

Comments

John Bode · Accepted Answer · 2011-05-09 18:42:29Z

1

If this is something you need to execute more than once, you might want to use a prepared statement. It's a bit more work, but it buys you some safety and performance. Not to mention you don't have to convert between strings and other types all the time.

answered May 9, 2011 at 18:42

John Bode

125k19 gold badges130 silver badges211 bronze badges

2 Comments

Jordi Renier Over a year ago

yeah, but now it's 50 lines of code... and it would change to +100... so i prefer to change :)

John Bode Over a year ago

@Jordi: c'est la logiciel. Obviously, there are tradeoffs; if this is a toy app for your own amusement, it may not be worth the extra effort. If this is something that's meant to go live, though, you definitely want to put that extra effort into it.

BlackBear · Accepted Answer · 2011-05-09 19:35:44Z

0

You have to convert MARKER_ID to string, then append it to the first part of your query and filanny append the rest of it.

char *query = malloc(80);
char num[11];

num = atoi(MARKER_ID);    

strcpy(query, "INSERT INTO markerherkenning (");
strcat(query, num);
strcat(query, ", DATETIME) values(1, CURRENT_TIME())");

edited May 9, 2011 at 19:35

answered May 9, 2011 at 18:07

BlackBear

23.1k10 gold badges53 silver badges90 bronze badges

4 Comments

BlackBear Over a year ago

@Bryan Drewery: yeah, fixed it. Thanks :)

Jordi Renier Over a year ago

and how do i implement this in my mysql query? mysql_query(conn, query); ? my program crashed @ char *num = atoi(var);

Jordi Renier Over a year ago

num = atoi(var) isn't recognized Error 11 error C2106: '=' : left operand must be l-value... :s

Jordi Renier Over a year ago

my solution... thanks for helping char *state; char buffer[256]; state = "INSERT INTO markerherkenning (MARKER_ID , DATETIME) VALUES('%d',CURRENT_TIME())"; len = sprintf(buffer, state, t); mysql_real_query(conn, buffer, len); this works :)

Collectives™ on Stack Overflow

mysql and c -- use variable in query

3 Answers 3

Comments

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related