OpenDNSSEC-enforcer  1.4.9
database_support_mysql.c
Go to the documentation of this file.
1 /*
2  * Copyright (c) 2008-2009 Nominet UK. All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions
6  * are met:
7  * 1. Redistributions of source code must retain the above copyright
8  * notice, this list of conditions and the following disclaimer.
9  * 2. Redistributions in binary form must reproduce the above copyright
10  * notice, this list of conditions and the following disclaimer in the
11  * documentation and/or other materials provided with the distribution.
12  *
13  * THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
14  * IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
15  * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
16  * ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY
17  * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
18  * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
19  * GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
20  * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
21  * IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
22  * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN
23  * IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
24  *
25  */
26 
27 /*+
28  * database_support - Database Utility Functions
29  *
30  * Description:
31  * Holds miscellaneous utility functions associated with the MySql
32  * database.
33 -*/
34 
35 #include <stdarg.h>
36 #include <string.h>
37 #include <stdio.h>
38 #include <time.h>
39 
40 #include <mysql.h>
41 
42 #include "ksm/dbsdef.h"
43 #include "ksm/database.h"
44 #include "ksm/debug.h"
45 #include "ksm/message.h"
46 #include "ksm/string_util.h"
47 #include "ksm/string_util2.h"
48 
49 #define MIN(x, y) ((x) < (y) ? (x) : (y))
50 #define MAX(x, y) ((x) > (y) ? (x) : (y))
51 
52 
53 
54 /*+
55  * DbExecuteSqlNoResult - Execute SQL Statement and Ignore Result
56  *
57  * Description:
58  * Executes the given SQL statement; any results are discarded.
59  *
60  * This function is useful for statements such as DELETE and INSERT.
61  *
62  * Arguments:
63  * DB_HANDLE handle
64  * Handle to the currently opened database.
65  *
66  * const char* stmt_str
67  * Statement to execute
68  *
69  * Returns:
70  * int
71  * Status return.
72  * 0 Success
73  * Other Error. A message will have been output.
74 -*/
75 
76 int DbExecuteSqlNoResult(DB_HANDLE handle, const char* stmt_str)
77 {
78  DB_RESULT result; /* Pointer to result string */
79  int status; /* Status return */
80 
81  status = DbExecuteSql(handle, stmt_str, &result);
82  if (status == 0) {
83  if (result) {
84 
85  /* Result given - get rid of it, we don't want it */
86 
87  status = MsgLog(DBS_UNEXRES, stmt_str);
88  DbFreeResult(result);
89  }
90  }
91 
92  return status;
93 }
94 
95 
96 /*+
97  * DbRowId - Return ID of Current Row
98  *
99  * Description:
100  * Returns the ID of the current row. This is assumed to be an auto-
101  * increment column at index 0 of the table.
102  *
103  * Arguments:
104  * DB_ROW row
105  * Row in question.
106  *
107  * DB_ID* id
108  * ID of the row is returned here.
109  *
110  * Returns:
111  * int
112  * Status return.
113  *
114  * 0 Success
115  * Other Error. A message will have been output.
116 -*/
117 
118 int DbRowId(DB_ROW row, DB_ID* id)
119 {
120  unsigned long rowid; /* ID of the row as a known type */
121  int status; /* Status return */
122 
123  if (id == NULL) {
124  return MsgLog(DBS_INVARG, "NULL id");
125  }
126 
127  status = DbUnsignedLong(row, 0, &rowid);
128  *id = (DB_ID) rowid; /* Do the conversion between types here */
129 
130  return status;
131 }
132 
133 
134 
135 
136 /*+
137  * DbInt - Return Integer from Field
138  *
139  * Description:
140  * Returns an integer value from the current row.
141  *
142  * Arguments:
143  * DB_ROW row
144  * Pointer to the row object.
145  *
146  * int field_index
147  * Index of the value required.
148  *
149  * int *value
150  * Value returned.
151  *
152  * Returns:
153  * int
154  * Status return:
155  * 0 Success
156  * Other Error accessing data. A message will have been output.
157 -*/
158 
159 int DbInt(DB_ROW row, int field_index, int *value)
160 {
161  char* buffer = NULL; /* Text buffer for returned string */
162  int status; /* Status return */
163 
164  /* Access the text in the field */
165 
166  status = DbString(row, field_index, &buffer);
167  if (status == 0) {
168 
169  /* Got the string, can we convert it? */
170 
171  if (buffer != NULL) {
172 
173  /* Not best-efforts - ignore trailing non-numeric values */
174 
175  status = StrStrtoi(buffer, value);
176  if (status == -1) {
177 
178  /* Could not translate the string to an integer */
179 
180  status = MsgLog(DBS_NOTINT, buffer);
181  *value = 0;
182  }
183  DbStringFree(buffer);
184  }
185  else {
186 
187  /* Field is NULL, return 0 */
188 
189  *value = 0;
190  }
191  }
192 
193  return status;
194 }
195 
196 
197 
198 /*+
199  * DbUnsignedLong - Return Unsigned Long from Field
200  *
201  * Description:
202  * Returns an integer value from the current row.
203  *
204  * Arguments:
205  * DB_ROW row
206  * Pointer to the row object.
207  *
208  * int field_index
209  * Index of the value required.
210  *
211  * unsigned long *value
212  * Value returned.
213  *
214  * Returns:
215  * int
216  * Status return:
217  * 0 Success
218  * Other Error accessing data. A message will have been output.
219 -*/
220 
221 int DbUnsignedLong(DB_ROW row, int field_index, unsigned long *value)
222 {
223  char* buffer = NULL; /* Text buffer for returned string */
224  int status; /* Status return */
225 
226  /* Access the text in the field */
227 
228  status = DbString(row, field_index, &buffer);
229  if (status == 0) {
230 
231  /* Got the string, can we convert it? */
232 
233  if (buffer != NULL) {
234 
235  /* Not best-efforts - ignore trailing non-numeric values */
236 
237  status = StrStrtoul(buffer, value);
238  if (status == -1) {
239 
240  /* Could not translate the string to an unsigned long */
241 
242  status = MsgLog(DBS_NOTINT, buffer);
243  *value = 0;
244  }
245  DbStringFree(buffer);
246  }
247  else {
248 
249  /* Field is NULL, return 0 */
250 
251  *value = 0;
252  }
253  }
254 
255  return status;
256 }
257 
258 
259 
260 /*+
261  * DbIntQuery - Perform Query Returning Single Integer
262  *
263  * Description:
264  * Many queries are of the form:
265  *
266  * SELECT COUNT(*) FROM ...
267  * or
268  * SELECT <single integer value> FROM ...
269  *
270  * This function performs the query and returns the single value.
271  *
272  * Arguments:
273  * DB_HANDLE handle
274  * Handle to the currently opened database.
275  *
276  * int* value
277  * Result of the query. Note that if the query returns no rows,
278  * a zero is returned.
279  *
280  * const char* query
281  * Query to run.
282  *
283  * Returns:
284  * int
285  * 0 Success
286  * Other Error (a message will have been output)
287 -*/
288 
289 int DbIntQuery(DB_HANDLE handle, int* value, const char* query)
290 {
291  DB_RESULT result = NULL; /* Result object */
292  DB_ROW row = NULL; /* Row object */
293  int status; /* Status return */
294 
295  status = DbExecuteSql(handle, query, &result);
296  if (status == 0) {
297 
298  /* Get first row */
299  status = DbFetchRow(result, &row);
300  if (status == 0) {
301  /* Got the row, so convert to integer */
302 
303  status = DbInt(row, 0, value);
304 
305  /* Query succeeded, but are there any more rows? */
306  if (DbFetchRow(result, &row) != -1) {
307  (void) MsgLog(DBS_TOOMANYROW, query); /* Too much data */
308  }
309  }
310  else
311  {
312  status = MsgLog(DBS_NORESULT); /* Query did not return a result */
313  }
314 
315  DbFreeResult(result);
316  DbFreeRow(row);
317  }
318 
319  return status;
320 }
321 
322 
323 /*+
324  * DbStringBuffer - Return String Value into User-Supplied Buffer
325  *
326  * Description:
327  * Returns string value from the current row into a user-supplied
328  * buffer. The returned value is truncated if required.
329  *
330  * Arguments:
331  * DB_ROW row
332  * Pointer to the row object.
333  *
334  * int field_index
335  * Index of the value required.
336  *
337  * char* buffer
338  * Null-terminated buffer into which the data is put. If the returned
339  * string is NULL, the buffer will contain a zero-length string. There
340  * is no way to distinguish between this and the database holding an
341  * empty string.
342  *
343  * size_t buflen
344  * Length of the buffer.
345  *
346  * Returns:
347  * int
348  * 0 Success
349  * Other Error. A message will have been output.
350 -*/
351 
352 int DbStringBuffer(DB_ROW row, int field_index, char* buffer, size_t buflen)
353 {
354  char* data; /* Data returned from DbString */
355  int status; /* Status return */
356 
357  if (row && (row->magic == DB_ROW_MAGIC) && buffer && (buflen != 0)) {
358 
359  /* Arguments OK, get the information */
360 
361  status = DbString(row, field_index, &data);
362  if (status == 0) {
363 
364  /* Success, copy the data into destination & free buffer
365  Note the StrStrncpy copes with data == NULL */
366 
367  StrStrncpy(buffer, data, buflen);
368  DbStringFree(data);
369  }
370  }
371  else {
372 
373  /* Invalid srguments, notify the user */
374 
375  status = MsgLog(DBS_INVARG, "DbStringBuffer");
376  }
377 
378  return status;
379 }
380 
381 
382 
383 /*+
384  * DbErrno - Return Last Error Number
385  *
386  * Description:
387  * Returns the numeric code associated with the last operation
388  * on this connection that gave an error.
389  *
390  * Arguments:
391  * DB_HANDLE handle
392  * Handle to an open database.
393  *
394  * Returns:
395  * int
396  * Error number.
397 -*/
398 
399 int DbErrno(DB_HANDLE handle)
400 {
401  return mysql_errno((MYSQL*) handle);
402 }
403 
404 
405 
406 /*+
407  * DbErrmsg - Return Last Error Message
408  *
409  * Description:
410  * Returns the last error on this connection. This is just an
411  * encapsulation of mysql_error.
412  *
413  * Arguments:
414  * DB_HANDLE handle
415  * Handle to an open database.
416  *
417  * Returns:
418  * const char*
419  * Error string. This should be copied and must not be freed.
420 -*/
421 
422 const char* DbErrmsg(DB_HANDLE handle)
423 {
424  return mysql_error((MYSQL*) handle);
425 }
426 
427 
428 /*+
429  * DbLastRowId - Return Last Row ID
430  *
431  * Description:
432  * Returns the ID field of the last row inserted.
433  *
434  * All tables are assumed to include an auto-incrementing ID field. Apart
435  * from providing the unique primary key, this is a relatively
436  * implementation-unique way of uniquely identifying a row in a table.
437  *
438  * Arguments:
439  * DB_HANDLE handle
440  * Handle to the database connection.
441  *
442  * DB_ID* id
443  * ID of the last row inserted (into any table) on this connection.
444  *
445  * Returns:
446  * int
447  * Status return
448  *
449  * 0 Success
450  * Other Error code. An error message will have been output.
451 -*/
452 
453 int DbLastRowId(DB_HANDLE handle, DB_ID* id)
454 {
455 
456  if (id == NULL) {
457  return MsgLog(DBS_INVARG, "NULL id");
458  }
459 
460  *id = (DB_ID) mysql_insert_id((MYSQL*) handle);
461 
462  /*
463  * In MySql, there is no error code; a value of 0 is returned if there
464  * is no matching row. In this case, convert it to an error code.
465  */
466 
467  return (*id != 0) ? 0 : DBS_NOSUCHROW;
468 }
469 
470 /*+
471  * DbQuoteString - Return quoted version of the input string
472  *
473  * Description:
474  * Return quoted version of the input string
475  *
476  * Arguments:
477  * DB_HANDLE handle
478  * Handle to the database connection. (MySQL checks character set of
479  * current connection).
480  *
481  * const char* in
482  * String to quote
483  *
484  * char* buffer
485  * Quoted string
486  *
487  * Returns:
488  * int
489  * Status return
490  *
491  * 0 Success
492  * Other Error code. An error message will have been output.
493 -*/
494 
495 int DbQuoteString(DB_HANDLE handle, const char* in, char* buffer, size_t buflen)
496 {
497 
498  unsigned long length = 0;
499 
500  if (in == NULL) {
501  return MsgLog(DBS_INVARG, "NULL input string to DbQuoteString");
502  }
503 
504  length = mysql_real_escape_string((MYSQL*) handle, buffer, in, (unsigned long) strlen(in));
505 
506  return ( length <= buflen ) ? 0 : 1;
507 }
508 
509 /*+
510  * DbDateDiff - Return SQL statement for a date plus or minus a delta
511  *
512  * Description:
513  * Return quoted version of the input string
514  *
515  * Arguments:
516  *
517  * const char* start
518  * Start date
519  *
520  * int delta
521  * Difference in seconds
522  *
523  * int sign
524  * -1 to subtract the delta, +1 to add
525  *
526  * char* buffer
527  * SQL string
528  *
529  * Returns:
530  * int
531  * Status return
532  *
533  * 0 Success
534  * Other Error code. An error message will have been output.
535 -*/
536 
537 int DbDateDiff(const char* start, int delta, int sign, char* buffer, size_t buflen)
538 {
539  int nchar;
540 
541  if (start == NULL) {
542  return MsgLog(DBS_INVARG, "NULL input string to DbDateDiff");
543  }
544 
545  if (sign == 1) {
546  nchar = snprintf(buffer, buflen,
547  "DATE_ADD('%s', INTERVAL %d SECOND)", start, delta);
548  }
549  else if (sign == -1) {
550  nchar = snprintf(buffer, buflen,
551  "DATE_ADD('%s', INTERVAL -%d SECOND)", start, delta);
552  }
553  else {
554  return MsgLog(DBS_INVARG, "Invalid sign to DbDateDiff");
555  }
556 
557  if (nchar >= (int) buflen || nchar < 0) {
558  return 1;
559  }
560 
561  return 0;
562 
563 }
void DbFreeResult(DB_RESULT result)
sqlite3 * DB_HANDLE
Definition: database.h:77
int DbUnsignedLong(DB_ROW row, int field_index, unsigned long *value)
int DbFetchRow(DB_RESULT result, DB_ROW *row)
int DbQuoteString(DB_HANDLE handle, const char *in, char *buffer, size_t buflen)
int DbInt(DB_ROW row, int field_index, int *value)
int DbRowId(DB_ROW row, DB_ID *id)
int MsgLog(int status,...)
Definition: message.c:335
int DbDateDiff(const char *start, int delta, int sign, char *buffer, size_t buflen)
#define DBS_INVARG
Definition: dbsdef.h:48
int DbString(DB_ROW row, int field_index, char **result)
#define DBS_TOOMANYROW
Definition: dbsdef.h:58
void StrStrncpy(char *dest, const char *src, size_t destlen)
Definition: string_util.c:176
unsigned long DB_ID
Definition: database.h:78
void DbFreeRow(DB_ROW row)
int DbIntQuery(DB_HANDLE handle, int *value, const char *query)
int DbExecuteSql(DB_HANDLE handle, const char *stmt_str, DB_RESULT *result)
int StrStrtoi(const char *string, int *value)
Definition: string_util2.c:506
#define DB_ROW_MAGIC
Definition: database.h:97
int DbLastRowId(DB_HANDLE handle, DB_ID *id)
int DbStringBuffer(DB_ROW row, int field_index, char *buffer, size_t buflen)
#define DBS_NORESULT
Definition: dbsdef.h:50
#define DBS_NOSUCHROW
Definition: dbsdef.h:51
int DbErrno(DB_HANDLE handle)
#define DBS_NOTINT
Definition: dbsdef.h:54
int DbExecuteSqlNoResult(DB_HANDLE handle, const char *stmt_str)
int StrStrtoul(const char *string, unsigned long *value)
Definition: string_util2.c:447
#define DBS_UNEXRES
Definition: dbsdef.h:59
const char * DbErrmsg(DB_HANDLE handle)
unsigned int magic
Definition: database.h:94
void DbStringFree(char *string)