Skip site navigation (1) Skip section navigation (2)

Strings with Null Bytes Causing Exceptions

From: User <user2037(at)ymail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Strings with Null Bytes Causing Exceptions
Date: 2009-06-03 19:25:36
Message-ID: 548495.696.qm@web59815.mail.ac4.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Strings with null characters produce exceptions when selected or inserted. Attempts to select messages with null bytes produces "ERROR: insufficient data left in message". And inserting produces "ERROR: invalid byte sequence for encoding \"UTF8\": 0x00".

Since a null character is a valid UTF code point why is it rejected by the JDBC driver?

It can work with Mysql and their JDBC driver.

import java.sql.*;

public class TestStringsWithNullBytes {
  public static void main(String[] args) {
    String noNulls = "No null bytes";
    String nulls = "Null bytes \000\000";
    try {
      Class.forName("org.postgresql.Driver");
      Connection db = DriverManager.getConnection(
        "jdbc:postgresql://localhost/test?user=test&password=secret");
      /*
      Class.forName("com.mysql.jdbc.Driver");
      Connection db = DriverManager.getConnection(
        "jdbc:mysql://localhost/test?user=test&password=secret");
        */
      db.setAutoCommit(true);
      tryQuery(db, "SELECT * FROM test");
      tryQuery(db, "DROP TABLE IF EXISTS test");
      tryQuery(db, "CREATE TABLE test (name TEXT)");
      tryQuery(db, "SELECT '" + noNulls + "'");
      tryQuery(db, "SELECT '" + nulls + "'");
      tryQuery(db, "INSERT INTO test (name) VALUES('" + noNulls + "')");
      tryQuery(db, "INSERT INTO test (name) VALUES('" + nulls + "')");
      tryPreparedQuery(db, "SELECT ?", noNulls);
      tryPreparedQuery(db, "SELECT ?", nulls);
      tryPreparedQuery(db, "INSERT INTO test (name) VALUES(?)", noNulls);
      tryPreparedQuery(db, "INSERT INTO test (name) VALUES(?)", nulls);
      tryQuery(db, "SELECT * FROM test");
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }

  private static void tryQuery(Connection db, String query) {
    try {
      Statement s = db.createStatement();
      if (s.execute(query)) {
        ResultSet rs = s.getResultSet();
        while(rs.next()) {
          System.out.println(escape(query) + ": " + escape(rs.getString(1)));
        }
      }
    }
    catch(Exception e) {
      System.err.println(escape(query) + ": " + e.getMessage());
    }
  }

  private static void tryPreparedQuery(Connection db, String query, String s) {
    try {
      PreparedStatement ps = db.prepareStatement(query);
      ps.setString(1, s);
      if (ps.execute()) {
        ResultSet rs = ps.getResultSet();
        while(rs.next()) {
          System.out.println(escape(query) + ": " + escape(rs.getString(1)));
        }
      }
    }
    catch(Exception e) {
      System.err.println(escape(query) + ": " + e.getMessage());
    }
  }

  private static StringBuilder escape(String s) {
    StringBuilder sb = new StringBuilder();
    for(int i = 0; i < s.length(); i++) {
      if ((32 > (int)s.charAt(i)) || (126 < (int)s.charAt(i))) {
        sb.append("<" + (int)s.charAt(i) + ">");
      }
      else {
        sb.append(s.charAt(i));
      }
    }
    return(sb);
  }
}



      


pgsql-jdbc by date

Next:From: Kris JurkaDate: 2009-06-03 20:19:05
Subject: Re: jdbc excpetions in pg
Previous:From: user2037Date: 2009-06-03 19:12:40
Subject: Strings with null characters produce exceptions when selected or inserted. Attempts to select messages with null bytes produces "ERROR: insufficient data left in message". And inserting produces "ERROR: invalid byte sequence for encoding \"UTF8\": 0x00". Since a null character is a valid UTF code point why is it rejected by the JDBC driver? The attached test can work with Mysql and their JDBC driver.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group