EPICS Home EPICS Controls Argonne National Laboratory

Experimental Physics and
Industrial Control System

Format page
for printing


Search Tech-talk
1994  1995  1996  1997  1998  1999  2000  2001  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013  2014  2015  2016  2017  2018  <2019>  2020  2021  2022  2023  2024  Index 1994  1995  1996  1997  1998  1999  2000  2001  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013  2014  2015  2016  2017  2018  <2019>  2020  2021  2022  2023  2024 
<== Date ==> <== Thread ==>
Subject: Re: MySQL Statement Doesn't Work
From: "Kasemir, Kay via Tech-talk" <[email protected]>
To: "Arms, Dohn A. via Tech-talk" <[email protected]>
Date: Wed, 4 Dec 2019 16:10:29 +0000
> 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
References:
MySQL Statement Doesn't Work Aaron Brown via Tech-talk
Re: MySQL Statement Doesn't Work Arms, Dohn A. via Tech-talk
Navigate by Date:
Prev: Re: MySQL Statement Doesn't Work Arms, Dohn A. via Tech-talk
Next: CU-3CX controller in SmarAct peiyu Quan via Tech-talk
Index: 1994  1995  1996  1997  1998  1999  2000  2001  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013  2014  2015  2016  2017  2018  <2019>  2020  2021  2022  2023  2024 
Navigate by Thread:
Prev: Re: MySQL Statement Doesn't Work Arms, Dohn A. via Tech-talk
Next: CU-3CX controller in SmarAct peiyu Quan via Tech-talk
Index: 1994  1995  1996  1997  1998  1999  2000  2001  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013  2014  2015  2016  2017  2018  <2019>  2020  2021  2022  2023  2024 
ANJ, 04 Dec 2019 Valid HTML 4.01! · Home · News · About · Base · Modules · Extensions · Distributions · Download ·
· Search · EPICS V4 · IRMIS · Talk · Bugs · Documents · Links · Licensing ·