sqlite3 - Added eDBadminGetCertificateInfo(...)

1 files changed, 92 insertions, 2 deletions

diff --git a/database/sqlite/administration.c b/database/sqlite/administration.c
index 3f8c286..4cc0f79 100644
--- a/database/sqlite/administration.c
+++ b/database/sqlite/administration.c
@@ -827,10 +827,100 @@ xmlDoc *eDBadminGetCertificateList(eurephiaCTX *ctx, const char *sortkeys) {
         return NULL;
 }
 
+// This function will search up all matching certificates from openvpn_certificates and return
+// the result as an XML document.
+//
+// Search criterias are set by sending the following XML document:
+//
+// <eurephia format="1">
+//    <certificate_info>
+//        <fieldMapping table="certificates">
+//            <{field name}>{field value}</{field name}>
+//            ...
+//            <{field name}>{field value}</{field name}>
+//        </fieldMapping>
+//    </certificate_info>
+// </eurephia>
+//
+// The found certificates will be sent in an XML like this:
+//
+// <eurephia format="1">
+//    <certificates certificates={number of found certs}">
+//        <certificate certid="{int}" depth="{int}" registered="{date+time}">
+//            <digest>{SHA1 digest of cert}</digest>
+//            <common_name>{(CN) common name of cert}</common_name>
+//            <organisation>{(O) organisation name of cert}</organisation>
+//            <email>{(emailAddr) e-mail address found in cert}</email>
+//        </certificate>
+//    </certificates>
+// </eurephia>
+//
+xmlDoc *eDBadminGetCertificateInfo(eurephiaCTX *ctx, xmlDoc *srchxml, const char *sortkeys) {
+        xmlDoc *certlist = NULL;
+        xmlNode *srch_n = NULL, *cert_n = NULL, *tmp_n = NULL;
+        eDBfieldMap *srch_map = NULL;
+        dbresult *res = NULL;
+        xmlChar tmp[2050];
+        char *dbsort = NULL;
+        int i;
 
+        assert( (ctx != NULL) && (srchxml != NULL) );
 
-xmlDoc *eDBadminGetCertificateInfo(eurephiaCTX *ctx, xmlDoc *srchkey) {
-        return NULL;
+        if( sortkeys != NULL ) {
+                dbsort = eDBmkSortKeyString(tbl_sqlite_certs, sortkeys);
+        }
+
+        srch_n = eurephiaXML_getRoot(ctx, srchxml, "certificate_info", 1);
+        if( srch_n == NULL ) {
+                eurephia_log(ctx, LOG_ERROR, 0, "Could not find a valid XML for looking up certificates");
+                return NULL;
+        }
+
+        srch_n = xmlFindNode(srch_n, "fieldMapping");
+        if( srch_n == NULL ) {
+                eurephia_log(ctx, LOG_ERROR, 0, "Could not find a valid XML for looking up certificates");
+                return NULL;
+        }
+
+        srch_map = eDBxmlMapping(ctx, tbl_sqlite_certs, NULL, srch_n);
+        assert( srch_map != NULL );
+
+        res = sqlite_query_mapped(ctx, SQL_SELECT,
+                                  "SELECT depth, digest, common_name, organisation, email, registered, certid"
+                                  "  FROM openvpn_certificates", NULL, srch_map, dbsort);
+        if( res == NULL ) {
+                eDBfreeMapping(srch_map);
+                eurephia_log(ctx, LOG_ERROR, 0, "Could not query the certificate table");
+                return NULL;
+        }
+
+        memset(&tmp, 0, 2050);
+        eurephiaXML_CreateDoc(ctx, 1, "certificates", &certlist, &cert_n);
+        xmlStrPrintf(tmp, 64, (xmlChar *) "%i", sqlite_get_numtuples(res));
+        xmlNewProp(cert_n, (xmlChar *) "certficates", (xmlChar *) tmp);
+
+        for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
+                tmp_n = xmlNewChild(cert_n, NULL, (xmlChar *) "certificate", NULL);
+
+                sqlite_xml_value(tmp_n, XML_ATTR, "certid", res, i, 6);
+                sqlite_xml_value(tmp_n, XML_ATTR, "depth", res, i, 0);
+                sqlite_xml_value(tmp_n, XML_ATTR, "registered", res, i, 5);
+                sqlite_xml_value(tmp_n, XML_NODE, "digest", res, i, 1);
+
+                xmlStrPrintf(tmp, 2048, (xmlChar *) "%.2048s", sqlite_get_value(res, i, 2));
+                // FIXME: replace_char((char *)&tmp, '_', ' ');
+                xmlNewChild(tmp_n, NULL, (xmlChar *) "common_name", tmp);
+
+                xmlStrPrintf(tmp, 2048, (xmlChar *) "%.2048s", sqlite_get_value(res, i, 3));
+                // FIXME: replace_char((char *)&tmp, '_', ' ');
+                xmlNewChild(tmp_n, NULL, (xmlChar *) "organisation", tmp);
+
+                sqlite_xml_value(tmp_n, XML_NODE, "email", res, i, 4);
+        }
+        sqlite_free_results(res);
+        eDBfreeMapping(srch_map);
+
+        return certlist;
 }