Skip to Content.
Sympa Menu

freetds - [freetds] freebcp adding escaping

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Andrew Punch <andrew.punch AT smart-associates.biz>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] freebcp adding escaping
  • Date: Wed, 3 Feb 2016 15:40:09 +1100

Hi,

I had a requirement to have properly formatted CSV output from freebcp. Unfortunately freebcp (and the original bcp) do not escape characters correctly. For example in a comma delimited file commas will not be escaped. Also the way NULLs are indicated can be inconvenient.

I hacked together a patch which I have attached. I would like to do a pull after a clean up but I have a few questions:

1. is dblib the best place to make the change?
2. what is the best structure to pass parameters and flags? Namely:
1. Flag for delimiter escaping
2. The actual delimiter (I notice that the column delimiter changes
to EOL for the last column)
3. Flag for NULL string replacement (instead of ASCII NUL)
4. NULL string replacement

diff --git a/include/dblib.h b/include/dblib.h
index 786ef6a..b85fa5b 100644
--- a/include/dblib.h
+++ b/include/dblib.h
@@ -66,8 +66,11 @@ typedef struct
 	DBINT column_len;
 	BYTE *terminator;
 	int term_len;
+	BYTE *null_value;
+	int null_value_len;
 	int tab_colnum;
 	int column_error;
+	BOOL escape;
 } BCP_HOSTCOLINFO;
 
 typedef struct 
diff --git a/src/dblib/bcp.c b/src/dblib/bcp.c
index a0708fe..9efffc8 100644
--- a/src/dblib/bcp.c
+++ b/src/dblib/bcp.c
@@ -85,6 +85,7 @@ static int rtrim(char *, int);
 static STATUS _bcp_read_hostfile(DBPROCESS * dbproc, FILE * hostfile, int *row_error);
 static int _bcp_readfmt_colinfo(DBPROCESS * dbproc, char *buf, BCP_HOSTCOLINFO * ci);
 static int _bcp_get_term_var(BYTE * pdata, BYTE * term, int term_len);
+static int _bcp_escape(TDS_UCHAR **buf, int buflen, char *terminator, int term_len);
 
 /*
  * "If a host file is being used ... the default data formats are as follows:
@@ -460,6 +461,9 @@ bcp_colfmt(DBPROCESS * dbproc, int host_colnum, int host_type, int host_prefixle
 	hostcol->terminator = terminator;
 	hostcol->term_len = host_termlen;
 	hostcol->tab_colnum = table_colnum;
+	hostcol->null_value = "\\N";
+	hostcol->null_value_len = 2;
+	hostcol->escape = TRUE;
 
 	return SUCCEED;
 }
@@ -739,8 +743,11 @@ _bcp_convert_out(DBPROCESS * dbproc, TDSCOLUMN *curcol, BCP_HOSTCOLINFO *hostcol
 		 * to be confused with a database NULL, which is denoted in the output
 		 * file with an empty string!)
 		 */
-		(*p_data)[0] = 0;
-		buflen = 1;
+		if (!hostcol->null_value_len) { 
+		    (*p_data)[0] = 0;
+		    buflen = 1;
+    } else
+		  buflen = 0;
 	} else if (is_numeric_type(hostcol->datatype)) {
 		TDS_NUMERIC *num = (TDS_NUMERIC *) (*p_data);
 		if (is_numeric_type(srctype)) {
@@ -766,6 +773,7 @@ _bcp_convert_out(DBPROCESS * dbproc, TDSCOLUMN *curcol, BCP_HOSTCOLINFO *hostcol
 		if (buflen < 0)
 			return buflen;
 
+		buflen = _bcp_escape(&cr.c, buflen, hostcol->terminator, hostcol->term_len);
 		if (buflen >= 256) {
 			free(*p_data);
 			*p_data = (TDS_UCHAR *) cr.c;
@@ -938,7 +946,11 @@ _bcp_exec_out(DBPROCESS * dbproc, DBINT * rows_copied)
 
 			curcol = resinfo->columns[hostcol->tab_colnum - 1];
 
-			if (curcol->column_cur_size < 0) {
+			if (curcol->column_cur_size == -1) {
+				// NULL. Assume null_value_len < 256
+				buflen = hostcol->null_value_len;
+				memcpy(data, hostcol->null_value, hostcol->null_value_len);    
+			} else if (curcol->column_cur_size < 0) {
 				buflen = 0;
 			} else {
 				buflen = _bcp_convert_out(dbproc, curcol, hostcol, &data, bcpdatefmt);
@@ -957,7 +969,7 @@ _bcp_exec_out(DBPROCESS * dbproc, DBINT * rows_copied)
 				buflen = buflen > hostcol->column_len ? hostcol->column_len : buflen;
 			}
 
-			if (buflen > 0) {
+			if (buflen > 0) {			  
 				if (fwrite(data, buflen, 1, hostfile) != 1)
 					goto write_error;
 			}
@@ -2305,3 +2317,49 @@ _bcp_free_storage(DBPROCESS * dbproc)
 	}
 }
 
+
+static int _bcp_escape(TDS_UCHAR **buf, int inbuflen, char *terminator, int term_len)
+{
+    TDS_UCHAR *outbuf = NULL, *inbuf = *buf;
+    int in_index, out_index;
+    int outbuflen=inbuflen;
+    
+    outbuf = (TDS_UCHAR*)malloc(MAX(inbuflen*2, 256));
+    
+    for (in_index=0,out_index=0;in_index<inbuflen;in_index++) {
+        BYTE character = inbuf[in_index];
+        
+        /* \\ \n \r \" \<terminator> \0 */
+        switch(character){
+            case '\\':
+            case '\n':
+            case '\r':
+            case '\"':
+            case '\'':
+            case '\t':
+            case ',':
+            case '|':
+                outbuflen++;
+                outbuf[out_index++] = '\\';
+                outbuf[out_index++] = character;
+                break;
+                
+            default:
+                // Escape if first character of terminator matches
+                // With multi-character terminators it may escape unecessarily
+                // which will take more space but will still be safe
+                // TODO: need to escape field delimiter and record delimiter properly
+                if (term_len && character == terminator[0]) {
+                    outbuflen++;
+                    outbuf[out_index++] = '\\';
+                    outbuf[out_index++] = character;
+                }
+                else
+                    outbuf[out_index++] = character;
+        }
+          
+    }
+    free(inbuf);
+    *buf = outbuf;
+    return outbuflen;
+}



Archive powered by MHonArc 2.6.24.

Top of Page