Re: MySQL Statement Doesn't Work

["Kasemir, Kay via Tech-talk" <tech-talk@aps.anl.gov>]

> I am not saying this solves your problem, but you are creating connections to the MySQL database with every event,

>and then not closing them, which will leak memory and sockets. You either need to close the connection after
> writing the event, or just connect once at the start of the program.

Along the lines of not solving the problem...

1) time(&timer);

    tm_info = localtime(&timer);

   strftime(buffer, 26, "%d-%m-%Y--%H:%M:%S", tm_info);

That's storing the current time of the client as the value's time stamp.

Better would be to use DBR_TIME_STRING instead of DBR_STRING, and then store the time stamp sent with the value, which is the time when the IOC obtained the value, without the delay of sending it via the network.

Actually best would be to check the native data type of the PV that you can see in the connection callback, and then use DBR_TIME_<that native type>.

2)    sprintf(query, "INSERT INTO PV_Table (Timestamp,Name,Value) VALUES (%s, %s, %s)", buffer, ca_name(eha.chid), pdata);

      mysql_query(conn, query);
This might be susceptible to SQL injection.

Imagine the record is a string record, and the value is "0);DROP table xxx;..".

When you constuct the statement as above, that would be executed:

"INSERT INTO PV_Table (Timestamp,Name,Value) VALUES (time, channel, 0);DROP table xxx;..)"

Need to tweak the example to handle the final ')', but you get the idea.

Look for "prepared statements" in the MySQL manual to prepare a statement with placeholders for timestamp, name, value.

Then keep using that by setting the timestamp, name, value of that prepared statement and execute the statement.

Re-using such a prepared statement is faster than creating a new one each time.

It's also safe, because a value of ".. DROP .." would simply be treated as a string for the value column, it's not executed.

Thanks,

Kay