restSQL 0.8.9 adds binary object support and improves security.

Binary Object Support

Binary object reads and writes are now supported in both Java and HTTP APIs. The tested column types are BLOB in MySQL and bytea in PostgreSQL.

In the Java API, use the framework-provided, BinaryObject, to wrap a byte array or translate from/to base 64 encoded string. The HTTP API (both XML and JSON forms) use base 64 encoded strings as well. For browser applications, the Javascript functions btoa() and atob() encode and decode base64 respectively.

Security Hardening

SQL generation was converted from standard name-value concatenation to prepared statements. The value portion of an SQL expression is set as a question mark (and with PostgreSQL a type case, e.g. ?::integer ) and a JDBC method setXxx() is called with the value. The database will process column values as values. Harmful execution statements cannot be injected into restSQL parameter values. Addtionally the framework now converts all String input parameter values for numeric and boolean column type to Inter, Long, Float, Double or Boolean. An exception is thrown if the value cannot be converted.

Example 1: the request restsql/res/Actor?id=47%20or%201%3d1 (decoded this is id=47 or 1=1 ) previously would return all rows, since the SQL generated would be select * from actor where id = 47; or 1=1 . restSQL will now return HTTP status 400 with the message Could not convert id value 47 or 1=1 to number .

Example 2: the request restsql/res/film?year=2000%3B%20DELETE%20FROM%20actor_genre (decoded this is year=2000;DELETE FROM actor_genre ) previously would return all films from 2000 and delete all rows from actor_genre since the SQL generated would be select * from film where year=2000; DELETE FROM actor_genre . restSQL will now return HTTP status 400 with the message Could not convert id value 1000; DELETE FROM actor_genre to number . changes

MySQL-specific implementation of ColumnMetaData was added.

Following are the excerpts with the additional properties

# DB-specific implementation classes - match the implementation to your database
# For MySQL:
# 	org.restsql.core.ColumnMetaData=org.restsql.core.impl.mysql.MySqlColumnMetaData
# For PostgreSQL:
# 	org.restsql.core.ColumnMetaData=org.restsql.core.impl.ColumnMetaDataImpl


If you are using MySQL with the built-in implementation classes, none of the preceeding properties are required. Please refer to the following examples:


Possibly change per above.