7 #include <mysql/errmsg.h>
9 //This static variable exists to handle initializing and finalizing the MySQL driver library
10 std::shared_ptr<int> MySQLJobProvider::lib_ref(
11 //Initializer: Store the return code as a pointer to an integer
12 new int( mysql_library_init( 0, NULL, NULL ) ),
14 //Finalizer: Check the pointer and free resources
17 //The library is not initialized
22 //The library did return an error when initializing
32 MySQLJobProvider::MySQLJobProvider( const std::string& server, const std::string& user, const std::string& password, const std::string& database ) {
33 if( !lib_ref || *lib_ref ) {
34 throw "MySQL library not initialized!";
37 connect( server, user, password, database );
40 bool MySQLJobProvider::connect( const std::string& server, const std::string& user, const std::string& password, const std::string& database ) {
42 conn = _connect( server, user, password, database );
47 std::shared_ptr<MYSQL> MySQLJobProvider::_connect( const std::string& server, const std::string& user, const std::string& password, const std::string& database ) {
48 MYSQL* tmp( mysql_init( NULL ) );
54 tmp = mysql_real_connect( tmp, server.c_str(), user.c_str(), password.c_str(), database.c_str(), 3306, NULL, CLIENT_COMPRESS );
61 return std::shared_ptr<MYSQL>(
70 bool MySQLJobProvider::disconnect() {
80 std::pair< int, std::shared_ptr<MYSQL_RES> > MySQLJobProvider::query( const std::string& query ) {
82 return std::make_pair( CR_SERVER_LOST, std::shared_ptr<MYSQL_RES>() );
85 int err = mysql_real_query( this->conn.get(), query.c_str(), query.size() );
88 throw std::string( "MySQL error: " ) + mysql_error( this->conn.get() );
92 std::shared_ptr<MYSQL_RES> res(
93 mysql_store_result( conn.get() ),
94 [c]( MYSQL_RES * r ) {
99 mysql_free_result( r );
102 return std::make_pair( err, res );
105 std::shared_ptr<Job> MySQLJobProvider::fetchJob() {
106 std::string q = "SELECT id, targetId, task, executeFrom, executeTo, warning FROM jobs WHERE state='open' AND warning < 3";
109 std::shared_ptr<MYSQL_RES> res;
111 std::tie( err, res ) = query( q );
117 unsigned int num = mysql_num_fields( res.get() );
119 MYSQL_ROW row = mysql_fetch_row( res.get() );
125 auto job = std::make_shared<Job>();
127 unsigned long* l = mysql_fetch_lengths( res.get() );
133 job->id = std::string( row[0], row[0] + l[0] );
134 job->target = std::string( row[1], row[1] + l[1] );
135 job->task = std::string( row[2], row[2] + l[2] );
136 job->from = std::string( row[3], row[3] + l[3] );
137 job->to = std::string( row[4], row[4] + l[4] );
138 job->warning = std::string( row[5], row[5] + l[5] );
140 for( unsigned int i = 0; i < num; i++ ) {
141 printf( "[%.*s] ", ( int ) l[i], row[i] ? row[i] : "NULL" );
149 std::string MySQLJobProvider::escape_string( const std::string& target ) {
151 throw "Not connected!";
156 result.resize( target.size() * 2 );
158 long unsigned int len = mysql_real_escape_string( conn.get(), const_cast<char*>( result.data() ), target.c_str(), target.size() );
160 result.resize( len );
165 void MySQLJobProvider::finishJob( std::shared_ptr<Job> job ) {
167 throw "Not connected!";
170 std::string q = "UPDATE jobs SET state='done' WHERE id='" + this->escape_string( job->id ) + "' LIMIT 1";
172 if( query( q ).first ) {
173 throw "No database entry found.";
177 void MySQLJobProvider::failJob( std::shared_ptr<Job> job ) {
179 throw "Not connected!";
182 std::string q = "UPDATE jobs SET warning = warning + 1 WHERE id='" + this->escape_string( job->id ) + "' LIMIT 1";
184 if( query( q ).first ) {
185 throw "No database entry found.";
189 std::shared_ptr<TBSCertificate> MySQLJobProvider::fetchTBSCert( std::shared_ptr<Job> job ) {
190 auto cert = std::make_shared<TBSCertificate>();
191 std::string q = "SELECT md, profile, csr_name, csr_type, keyname FROM certs INNER JOIN profiles ON profiles.id = certs.profile WHERE certs.id='" + this->escape_string( job->target ) + "'";
195 std::shared_ptr<MYSQL_RES> res;
197 std::tie( err, res ) = query( q );
203 MYSQL_ROW row = mysql_fetch_row( res.get() );
209 unsigned long* l = mysql_fetch_lengths( res.get() );
215 std::string profileName = std::string( row[4], row[4] + l[4] );
217 cert->md = std::string( row[0], row[0] + l[0] );
218 std::string profileId = std::string( row[1], row[1] + l[1] );
220 while( profileId.size() < 4 ) {
221 profileId = "0" + profileId;
224 cert->profile = profileId + "-" + profileName;
226 cert->csr = std::string( row[2], row[2] + l[2] );
227 cert->csr_type = std::string( row[3], row[3] + l[3] );
229 cert->SANs = std::vector<std::shared_ptr<SAN>>();
231 q = "SELECT contents, type FROM subjectAlternativeNames WHERE certId='" + this->escape_string( job->target ) + "'";
232 std::tie( err, res ) = query( q );
235 std::cout << mysql_error( this->conn.get() );
239 std::cout << "Fetching SANs" << std::endl;
241 while( ( row = mysql_fetch_row( res.get() ) ) ) {
242 unsigned long* l = mysql_fetch_lengths( res.get() );
248 std::shared_ptr<SAN> nSAN = std::shared_ptr<SAN>( new SAN() );
249 nSAN->content = std::string( row[0], row[0] + l[0] );
250 nSAN->type = std::string( row[1], row[1] + l[1] );
251 cert->SANs.push_back( nSAN );
254 q = "SELECT name, value FROM certAvas WHERE certid='" + this->escape_string( job->target ) + "'";
255 std::tie( err, res ) = query( q );
258 std::cout << mysql_error( this->conn.get() );
263 while( ( row = mysql_fetch_row( res.get() ) ) ) {
264 unsigned long* l = mysql_fetch_lengths( res.get() );
270 std::shared_ptr<AVA> nAVA = std::shared_ptr<AVA>( new AVA() );
271 nAVA->name = std::string( row[0], row[0] + l[0] );
272 nAVA->value = std::string( row[1], row[1] + l[1] );
273 cert->AVAs.push_back( nAVA );
279 void MySQLJobProvider::writeBack( std::shared_ptr<Job> job, std::shared_ptr<SignedCertificate> res ) {
281 throw "Error while writing back";
284 std::string id = "SELECT id FROM cacerts WHERE keyname='" + this->escape_string( res->ca_name ) + "'";
287 std::shared_ptr<MYSQL_RES> resu;
288 std::tie( err, resu ) = query( id );
291 throw "Error while looking ca cert id";
294 MYSQL_ROW row = mysql_fetch_row( resu.get() );
295 unsigned long* l = mysql_fetch_lengths( resu.get() );
300 if( query( "INSERT INTO cacerts SET keyname= '" + this->escape_string( res->ca_name ) + "', subroot = 0" ).first ) {
301 throw "Error while inserting new ca cert";
304 my_ulonglong insert_id = mysql_insert_id( conn.get() );
306 read_id = std::to_string( insert_id );
308 read_id = std::string( row[0], row[0] + l[0] );
311 std::string q = "UPDATE certs SET crt_name='" + this->escape_string( res->crt_name ) + "', serial='" + this->escape_string( res->serial ) + "', caId = '" + this->escape_string( read_id ) + "', created='" + this->escape_string( res->before ) + "', expire='" + this->escape_string( res->after ) + "' WHERE id='" + this->escape_string( job->target ) + "' LIMIT 1";
312 // TODO write more thingies back
314 if( query( q ).first ) {
315 throw "Error while writing back";
319 std::pair<std::string, std::string> MySQLJobProvider::getRevocationInfo( std::shared_ptr<Job> job ) {
320 std::string q = "SELECT certs.serial, cacerts.keyname FROM certs INNER JOIN cacerts ON certs.caId = cacerts.id WHERE certs.id = '" + this->escape_string( job->target ) + "' ";
322 std::shared_ptr<MYSQL_RES> resu;
323 std::tie( err, resu ) = query( q );
326 throw "Error while looking ca cert id";
329 MYSQL_ROW row = mysql_fetch_row( resu.get() );
330 unsigned long* l = mysql_fetch_lengths( resu.get() );
333 throw "Error while inserting new ca cert";
336 return std::pair<std::string, std::string>( std::string( row[0], row[0] + l[0] ), std::string( row[1], row[1] + l[1] ) );
339 void MySQLJobProvider::writeBackRevocation( std::shared_ptr<Job> job, std::string date ) {
340 if( query( "UPDATE certs SET revoked = '" + this->escape_string( date ) + "' WHERE id = '" + this->escape_string( job->target ) + "'" ).first ) {
341 throw "Error while writing back revocation";