mdbtools-dev Mailing List for MDB Tools (Page 10)

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

* Moved sanitize_name() into its own header/source files, since the same
   function code had been implemented in two different source files
   (mdb-export.c and mdb-schema.c).

 * The enumerated type for specifying the column-data-type is now called
   MdbColDataType rather than the previous anonymous enum.

 * The open-file-failed error-messages in mdb-schema now contains the filename.
   Several error messages from mdb-export and mdb-schema now include the
   program-name.

 * mdb-export: boolean columns export as "true"/"false", rather than "0"/"1".

 * sanitize_names(): fixed bug: potential buffer-overflow-error.

 * sanitize_names(): looks for some SQL reserved words and if found attempts to
   mangle them to generate legal SQL.

Signed-off-by: David Favro <mdb...@me...>
---
 AUTHORS                  |    3 ++
 include/mdbtools.h       |    4 +-
 src/util/Makefile.am     |    3 ++
 src/util/mdb-export.c    |   35 ++++++---------------
 src/util/mdb-schema.c    |   36 +++++----------------
 src/util/sanitize_name.c |   78 ++++++++++++++++++++++++++++++++++++++++++++++
 src/util/sanitize_name.h |   29 +++++++++++++++++
 7 files changed, 134 insertions(+), 54 deletions(-)
 create mode 100644 src/util/sanitize_name.c
 create mode 100644 src/util/sanitize_name.h

diff --git a/AUTHORS b/AUTHORS
index f639990..e08069c 100644
--- a/AUTHORS
+++ b/AUTHORS
@@ -24,3 +24,6 @@ David Mansfield <mdb...@dm...>
 
 Jeff Smith <why...@ya...>
 	Patches too numerous to enumerate.
+
+David Favro <mdb...@me...>
+	Enhancements/fixes: sanitization of names for SQL.
diff --git a/include/mdbtools.h b/include/mdbtools.h
index 8e33df3..25fe793 100644
--- a/include/mdbtools.h
+++ b/include/mdbtools.h
@@ -74,7 +74,7 @@ enum {
 	MDB_DATABASE_PROPERTY,
 	MDB_ANY = -1
 };
-enum {
+typedef enum MdbColDataType {
 	MDB_BOOL = 0x01,
 	MDB_BYTE = 0x02,
 	MDB_INT = 0x03,
@@ -88,7 +88,7 @@ enum {
 	MDB_MEMO = 0x0c,
 	MDB_REPID = 0x0f,
 	MDB_NUMERIC = 0x10
-};
+} MdbColDataType;
 
 /* SARG operators */
 enum {
diff --git a/src/util/Makefile.am b/src/util/Makefile.am
index c872aec..8ecb716 100644
--- a/src/util/Makefile.am
+++ b/src/util/Makefile.am
@@ -7,3 +7,6 @@ LDADD	=	../libmdb/libmdb.la
 if SQL
 mdb_sql_LDADD = ../libmdb/libmdb.la ../sql/libmdbsql.la $(LIBREADLINE)
 endif
+
+mdb_export_SOURCES = mdb-export.c sanitize_name.c sanitize_name.h
+mdb_schema_SOURCES = mdb-schema.c sanitize_name.c sanitize_name.h
diff --git a/src/util/mdb-export.c b/src/util/mdb-export.c
index 8aa5290..e60e59a 100644
--- a/src/util/mdb-export.c
+++ b/src/util/mdb-export.c
@@ -23,16 +23,17 @@
 #include "dmalloc.h"
 #endif
 
+#include "sanitize_name.h"
+
 #undef MDB_BIND_SIZE
 #define MDB_BIND_SIZE 200000
 
 #define is_text_type(x) (x==MDB_TEXT || x==MDB_MEMO || x==MDB_SDATETIME)
 
-static char *sanitize_name(char *str, int sanitize);
 static char *escapes(char *s);
 
 void
-print_col(gchar *col_val, int quote_text, int col_type, char *quote_char, char *escape_char)
+print_col(gchar *col_val, int quote_text, MdbColDataType col_type, char *quote_char, char *escape_char)
 {
 	gchar *s;
 
@@ -50,6 +51,8 @@ print_col(gchar *col_val, int quote_text, int col_type, char *quote_char, char *
 			else fprintf(stdout,"%c",*s);
 		}
 		fprintf(stdout,quote_char);
+	} else if ( col_type == MDB_BOOL ) {
+		fputs( ((*col_val == '0') ? "false" : "true"), stdout );
 	} else {
 		fprintf(stdout,"%s",col_val);
 	}
@@ -148,6 +151,7 @@ main(int argc, char **argv)
 		g_free (quote_char);
 		if (escape_char) g_free (escape_char);
 		mdb_exit();
+		fprintf( stderr, "%s: error opening \"%s\"\n", argv[0], argv[optind] );
 		exit(1);
 	}
 
@@ -174,11 +178,11 @@ main(int argc, char **argv)
 	}
 	if (header_row) {
 		col=g_ptr_array_index(table->columns,0);
-		fprintf(stdout,"%s",sanitize_name(col->name,sanitize));
+		fprintf(stdout,"%s",sanitize_name(col->name,sanitize,argv[0]));
 		for (j=1;j<table->num_cols;j++) {
 			col=g_ptr_array_index(table->columns,j);
 			fprintf(stdout,delimiter);
-			fprintf(stdout,"%s",sanitize_name(col->name,sanitize));
+			fprintf(stdout,"%s",sanitize_name(col->name,sanitize,argv[0]));
 		}
 		fprintf(stdout,"\n");
 	}
@@ -187,11 +191,11 @@ main(int argc, char **argv)
 
 		if (insert_statements) {
 			fprintf(stdout, "INSERT INTO %s (",
-				sanitize_name(argv[optind + 1],sanitize));
+				sanitize_name(argv[optind + 1],sanitize,argv[0]));
 			for (j=0;j<table->num_cols;j++) {
 				if (j>0) fprintf(stdout, ", ");
 				col=g_ptr_array_index(table->columns,j);
-				fprintf(stdout,"%s", sanitize_name(col->name,sanitize));
+				fprintf(stdout,"%s", sanitize_name(col->name,sanitize,argv[0]));
 			}
 			fprintf(stdout, ") VALUES (");
 		}
@@ -231,25 +235,6 @@ main(int argc, char **argv)
 	exit(0);
 }
 
-static char *sanitize_name(char *str, int sanitize)
-{
-	static char namebuf[256];
-	char *p = namebuf;
-
-	if (!sanitize)
-		return str;
-
-	while (*str) {
-		*p = isalnum(*str) ? *str : '_';
-		p++;
-		str++;
-	}
-
-	*p = 0;
-
-	return namebuf;
-}
-
 static char *escapes(char *s)
 {
 	char *d = (char *) g_strdup(s);
diff --git a/src/util/mdb-schema.c b/src/util/mdb-schema.c
index b142711..9216b20 100644
--- a/src/util/mdb-schema.c
+++ b/src/util/mdb-schema.c
@@ -24,8 +24,9 @@
 #include "dmalloc.h"
 #endif
 
-static char *sanitize_name(char *str, int sanitize);
-static void generate_table_schema(MdbCatalogEntry *entry, char *namespace, int sanitize);
+#include "sanitize_name.h"
+
+static void generate_table_schema(MdbCatalogEntry *entry, char *namespace, int sanitize, const char * prog_name);
 
 int
 main (int argc, char **argv)
@@ -67,7 +68,7 @@ main (int argc, char **argv)
 	/* open the database */
 	mdb = mdb_open (argv[optind], MDB_NOFLAGS);
 	if (!mdb) {
-		fprintf(stderr, "Could not open file\n");
+		fprintf(stderr, "%s: could not open file: \"%s\"\n", argv[0], argv[optind] );
 		mdb_exit();
 		exit(1);
 	}
@@ -101,7 +102,7 @@ main (int argc, char **argv)
 		if (entry->object_type == MDB_TABLE) {
 			if ((tabname && !strcmp(entry->object_name, tabname))
 			 || (!tabname && mdb_is_user_table(entry))) {
-				generate_table_schema(entry, namespace, s);
+				generate_table_schema(entry, namespace, s, argv[0]);
 			}
 		}
 	}
@@ -122,7 +123,7 @@ main (int argc, char **argv)
 	exit(0);
 }
 static void
-generate_table_schema(MdbCatalogEntry *entry, char *namespace, int sanitize)
+generate_table_schema(MdbCatalogEntry *entry, char *namespace, int sanitize, const char * prog_name)
 {
 	MdbTableDef *table;
 	MdbHandle *mdb = entry->mdb;
@@ -131,11 +132,11 @@ generate_table_schema(MdbCatalogEntry *entry, char *namespace, int sanitize)
 
 	/* drop the table if it exists */
 	fprintf (stdout, "DROP TABLE %s%s;\n", (namespace) ? namespace : "",
-		sanitize_name(entry->object_name, sanitize));
+		sanitize_name(entry->object_name, sanitize, prog_name));
 
 	/* create the table */
 	fprintf (stdout, "CREATE TABLE %s%s\n", (namespace) ? namespace : "",
-		sanitize_name(entry->object_name, sanitize));
+		sanitize_name(entry->object_name, sanitize, prog_name));
 	fprintf (stdout, " (\n");
 
 	table = mdb_read_table (entry);
@@ -148,7 +149,7 @@ generate_table_schema(MdbCatalogEntry *entry, char *namespace, int sanitize)
 	for (i = 0; i < table->num_cols; i++) {
 		col = g_ptr_array_index (table->columns, i);
 
-		fprintf (stdout, "\t%s\t\t\t%s", sanitize_name(col->name,sanitize),
+		fprintf (stdout, "\t%s\t\t\t%s", sanitize_name(col->name, sanitize, prog_name),
 		mdb_get_coltype_string (mdb->default_backend, col->col_type));
 
 		if (mdb_coltype_takes_length(mdb->default_backend,
@@ -173,22 +174,3 @@ generate_table_schema(MdbCatalogEntry *entry, char *namespace, int sanitize)
 
 	mdb_free_tabledef (table);
 }
-
-static char *sanitize_name(char *str, int sanitize)
-{
-	static char namebuf[256];
-	char *p = namebuf;
-
-	if (!sanitize)
-		return str;
-
-	while (*str) {
-		*p = isalnum(*str) ? *str : '_';
-		p++;
-		str++;
-	}
-
-	*p = 0;
-
-	return namebuf;
-}
diff --git a/src/util/sanitize_name.c b/src/util/sanitize_name.c
new file mode 100644
index 0000000..66d9640
--- /dev/null
+++ b/src/util/sanitize_name.c
@@ -0,0 +1,78 @@
+/* MDB Tools - A library for reading MS Access database file
+ * Copyright (C) 2000 Brian Bruns
+ * Portions copyright (C) 2008 David Favro
+ *
+ * This program is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU General Public License as published by
+ * the Free Software Foundation; either version 2 of the License, or
+ * (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
+ */
+
+
+#include "sanitize_name.h"
+
+#include <stdio.h> 	/* fprintf(), stderr */
+#include <ctype.h> 	/* isalnum(), isdigit() */
+#include <stdlib.h> /* exit() */
+
+
+static const char * const RESERVED [] =
+	{
+		"CHECK" ,
+	};
+#define	N_RESERVED (sizeof(RESERVED)/sizeof(*RESERVED))
+
+
+char * sanitize_name(char *str, int sanitize, const char * prog_name)
+    {
+	static char namebuf[256];
+	char *p = namebuf;
+
+	if (!sanitize)
+		return str;
+
+	const char * sPtr = str;
+
+	if ( isdigit(*sPtr) )
+		*p++ = '_';
+	else
+		{
+		// Check for SQL keywords thay may be used as identifiers in Access.
+		// For the moment, just do a linear search through the keywords, with a
+		// string comparison for each.  This probably should be replaced with a
+		// more sophisticated data structure such as a hash table.
+
+		const char * const * rPtr;
+		for ( rPtr = RESERVED + N_RESERVED ; rPtr-- != RESERVED ; )
+			if ( strcasecmp( str, *rPtr ) == 0 )
+				{
+				*p++ = '_';
+				break; // **** BREAK HERE ****
+				}
+		}
+
+	char * const limit = namebuf + sizeof(namebuf);
+
+	while (*sPtr) {
+		*p = isalnum(*sPtr) ? *sPtr : '_';
+		if ( p++ >= limit ) {
+			fprintf( stderr, "%s: failed assertion: table-name buffer-overflow (%s)\n",
+						prog_name, str );
+			exit( 1 );
+		}
+		sPtr++;
+	}
+
+	*p = '\0';
+
+	return namebuf;
+    }
diff --git a/src/util/sanitize_name.h b/src/util/sanitize_name.h
new file mode 100644
index 0000000..1fc9bcc
--- /dev/null
+++ b/src/util/sanitize_name.h
@@ -0,0 +1,29 @@
+/* MDB Tools - A library for reading MS Access database file
+ * Copyright (C) 2000 Brian Bruns
+ * Portions copyright (C) 2008 David Favro
+ *
+ * This program is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU General Public License as published by
+ * the Free Software Foundation; either version 2 of the License, or
+ * (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
+ */
+
+
+
+#ifndef	__sanitize_name_h
+#define __sanitize_name_h
+
+
+extern char * sanitize_name( char *str, int sanitize, const char * prog_name );
+
+
+#endif /* __sanitize_name_h */
-- 
1.7.0.4





2000	Jan	Feb (1)	Mar (53)	Apr (28)	May (5)	Jun (7)	Jul (16)	Aug (15)	Sep (10)	Oct (1)	Nov	Dec (1)
2001	Jan (9)	Feb (7)	Mar (1)	Apr (7)	May (6)	Jun	Jul (15)	Aug (10)	Sep (2)	Oct (12)	Nov (3)	Dec (2)
2002	Jan (2)	Feb (12)	Mar (33)	Apr (30)	May (5)	Jun (18)	Jul (18)	Aug (47)	Sep (8)	Oct (7)	Nov (8)	Dec (13)
2003	Jan (48)	Feb (8)	Mar (10)	Apr (30)	May (6)	Jun (8)	Jul (19)	Aug (36)	Sep (19)	Oct (16)	Nov (11)	Dec (17)
2004	Jan (11)	Feb (22)	Mar (52)	Apr (45)	May (18)	Jun (72)	Jul (14)	Aug (31)	Sep (19)	Oct (27)	Nov (19)	Dec (25)
2005	Jan (16)	Feb (46)	Mar (50)	Apr (3)	May (21)	Jun (3)	Jul (24)	Aug (33)	Sep (25)	Oct (23)	Nov (30)	Dec (20)
2006	Jan (12)	Feb (11)	Mar (8)	Apr (15)	May (27)	Jun (15)	Jul (19)	Aug (5)	Sep (9)	Oct (1)	Nov (2)	Dec (3)
2007	Jan	Feb (3)	Mar (18)	Apr (5)	May (9)	Jun	Jul (10)	Aug (3)	Sep (8)	Oct (1)	Nov (7)	Dec (9)
2008	Jan (2)	Feb	Mar (10)	Apr (4)	May	Jun (5)	Jul (9)	Aug	Sep (1)	Oct	Nov	Dec (8)
2009	Jan	Feb	Mar	Apr	May (1)	Jun	Jul	Aug	Sep	Oct (11)	Nov (1)	Dec (20)
2010	Jan	Feb (2)	Mar	Apr (7)	May	Jun (23)	Jul (3)	Aug (6)	Sep (1)	Oct (4)	Nov (1)	Dec
2011	Jan (1)	Feb (26)	Mar (25)	Apr (11)	May (5)	Jun (5)	Jul (2)	Aug (39)	Sep (12)	Oct (6)	Nov	Dec
2012	Jan (19)	Feb (5)	Mar	Apr	May	Jun (7)	Jul	Aug (8)	Sep	Oct (3)	Nov (2)	Dec (3)
2013	Jan (6)	Feb	Mar (1)	Apr	May (7)	Jun (5)	Jul (2)	Aug	Sep (1)	Oct (2)	Nov	Dec
2014	Jan (4)	Feb	Mar (2)	Apr	May	Jun	Jul	Aug (1)	Sep	Oct	Nov	Dec
2015	Jan	Feb	Mar	Apr	May (1)	Jun	Jul	Aug	Sep	Oct	Nov	Dec (1)
2016	Jan (5)	Feb	Mar (1)	Apr (1)	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2017	Jan	Feb (1)	Mar	Apr	May	Jun (2)	Jul	Aug	Sep	Oct	Nov	Dec
2018	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov (1)	Dec