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