fix: Handling of NULL bytes in Postgresql Text fields (#5249)

shimonp21 · web-flow · commit 936c311e6cd5 · 2022-12-01T12:23:02.000Z
Required to fix batch-write-failures due to postgresql TEXT fields containing NULL bytes.

```
2022-11-30T12:00:11Z ERR failed to execute batch with pgerror error="ERROR: invalid byte sequence for encoding \"UTF8\": 0x00 (SQLSTATE 22021)" module=pg-dest
```
diff --git a/plugins/destination/postgresql/client/transformer.go b/plugins/destination/postgresql/client/transformer.go
@@ -1,6 +1,8 @@
 package client
 
 import (
+	"strings"
+
 	"github.com/cloudquery/plugin-sdk/schema"
 	"github.com/jackc/pgtype"
 )
@@ -54,15 +56,15 @@ func (*Client) TransformJSON(v *schema.JSON) interface{} {
 
 func (*Client) TransformText(v *schema.Text) interface{} {
 	return &pgtype.Text{
-		String: v.Str,
+		String: stripNulls(v.Str),
 		Status: cqStatusToPgStatus[v.Status],
 	}
 }
 
 func (*Client) TransformTextArray(v *schema.TextArray) interface{} {
 	r := pgtype.TextArray{}
 	for _, e := range v.Elements {
-		r.Elements = append(r.Elements, pgtype.Text{String: e.Str, Status: cqStatusToPgStatus[e.Status]})
+		r.Elements = append(r.Elements, pgtype.Text{String: stripNulls(e.Str), Status: cqStatusToPgStatus[e.Status]})
 	}
 	r.Status = cqStatusToPgStatus[v.Status]
 	for _, d := range v.Dimensions {
@@ -181,3 +183,7 @@ func (c *Client) TransformMacaddrArray(v *schema.MacaddrArray) interface{} {
 	}
 	return &r
 }
+
+func stripNulls(s string) string {
+	return strings.ReplaceAll(s, "\x00", "")
+}
diff --git a/plugins/destination/sqlite/client/transformer.go b/plugins/destination/sqlite/client/transformer.go
@@ -1,6 +1,10 @@
 package client
 
-import "github.com/cloudquery/plugin-sdk/schema"
+import (
+	"strings"
+
+	"github.com/cloudquery/plugin-sdk/schema"
+)
 
 func (*Client) TransformBool(v *schema.Bool) interface{} {
 	return v.String()
@@ -27,11 +31,11 @@ func (*Client) TransformJSON(v *schema.JSON) interface{} {
 }
 
 func (*Client) TransformText(v *schema.Text) interface{} {
-	return v.String()
+	return stripNulls(v.String())
 }
 
 func (*Client) TransformTextArray(v *schema.TextArray) interface{} {
-	return v.String()
+	return stripNulls(v.String())
 }
 
 func (*Client) TransformTimestamptz(v *schema.Timestamptz) interface{} {
@@ -69,3 +73,7 @@ func (*Client) TransformMacaddr(v *schema.Macaddr) interface{} {
 func (*Client) TransformMacaddrArray(v *schema.MacaddrArray) interface{} {
 	return v.String()
 }
+
+func stripNulls(s string) string {
+	return strings.ReplaceAll(s, "\x00", "")
+}

Original file line number	Diff line number	Diff line change
`@@ -1,6 +1,8 @@`
`1`	`1`	`package client`
`2`	`2`
`3`	`3`	`import (`
	`4`	`+ "strings"`
	`5`	`+`
`4`	`6`	`"github.com/cloudquery/plugin-sdk/schema"`
`5`	`7`	`"github.com/jackc/pgtype"`
`6`	`8`	`)`
`@@ -54,15 +56,15 @@ func (Client) TransformJSON(v schema.JSON) interface{} {`
`54`	`56`
`55`	`57`	`func (Client) TransformText(v schema.Text) interface{} {`
`56`	`58`	`return &pgtype.Text{`
`57`		`- String: v.Str,`
	`59`	`+ String: stripNulls(v.Str),`
`58`	`60`	`Status: cqStatusToPgStatus[v.Status],`
`59`	`61`	`}`
`60`	`62`	`}`
`61`	`63`
`62`	`64`	`func (Client) TransformTextArray(v schema.TextArray) interface{} {`
`63`	`65`	`r := pgtype.TextArray{}`
`64`	`66`	`for _, e := range v.Elements {`
`65`		`- r.Elements = append(r.Elements, pgtype.Text{String: e.Str, Status: cqStatusToPgStatus[e.Status]})`
	`67`	`+ r.Elements = append(r.Elements, pgtype.Text{String: stripNulls(e.Str), Status: cqStatusToPgStatus[e.Status]})`
`66`	`68`	`}`
`67`	`69`	`r.Status = cqStatusToPgStatus[v.Status]`
`68`	`70`	`for _, d := range v.Dimensions {`
`@@ -181,3 +183,7 @@ func (c Client) TransformMacaddrArray(v schema.MacaddrArray) interface{} {`
`181`	`183`	`}`
`182`	`184`	`return &r`
`183`	`185`	`}`
	`186`	`+`
	`187`	`+func stripNulls(s string) string {`
	`188`	`+ return strings.ReplaceAll(s, "\x00", "")`
	`189`	`+}`