JsonSQL - JsonSQL distribution. A collection of modules for generating safe SQL from JSON strings.
version 0.41
This is a set of modules used to turn a JSON string representing an SQL query into an appropriate SQL statement.
For example,
use JsonSQL::Query::Select; my $jsonString = '{ "fields": [ {"column": "*"} ], "from": [ {"table": "my_table"} ] }'; my $whitelisting_rules = [ { schema => '#anySchema', 'my_table' => [ '#anyColumn' ] } ]; my $selectObj = JsonSQL::Query::Select->new($whitelisting_rules, $jsonString); my ( $sql, $binds ) = $selectObj->get_select;
Generates:
$sql = SELECT * FROM "my_table"; $binds = <arrayref of parameterized values, if applicable>
Now you can go ahead and use $sql and $binds directly with the DBI module to do the query.
The purpose of this distribution is to provide a reasonably safe mechanism for SQL query generation using data from untrusted sources, namely web browsers. JSON is a convenient format native to JavaScript (ECMAScript), which can be translated to and from Perl objects fairly easily. JSON was selected to provide a structured format for representing SQL statements such that it can be validated, checked for appropriate access restrictions, and used to generate a well-formed and parameterized SQL statement that can be passed off to the DBI module.
The format is somewhat verbose in a few places, with the idea that the user/developer needs to be very explicit when passing parameters to the query. This makes it well-suited for handling untrusted data (for example, from HTML forms), but if you just need a basic SQL generator, you would probably be better off looking at SQL::Abstract or SQL::Maker instead.
A simple SELECT statement,
{ "fields": [ {"column": "*"} ], "from": [ {"table": "my_table"} ] }
A more complicated SELECT statement,
{ "fields": [ {"column": "field1"}, {"column": "field2", "alias": "test"} ], "from": [ {"table": "table1", "schema": "MySchema"} ], "where": { "and": [ { "eq": {"field": {"column": "field2"}, "value": "Test.Field2"} }, { "eq": {"field": {"column": "field1"}, "value": "453.6"} }, { "or": [ { "eq": {"field": {"column": "field2"}, "value": "field3"} }, { "gt": {"field": {"column": "field3"}, "value": "45"} } ]} ] } }
A SELECT statement with JOINs,
{ "fields": [ {"column": "field1"}, {"column": "field2", "alias": "test"} ], "joins": [ {"jointype": "inner", "from": {"table": "table1", "schema": "MySchema"}, "to": {"table": "table2", "schema": "MySchema"}, "on": {"eq": {"field": {"column": "field2"}, "value": {"column": "field1"}} }} ], "where": { "and": [ { "eq": {"field": {"column": "field2"}, "value": "Test.Field2"} }, { "eq": {"field": {"column": "field1"}, "value": "453.6"} }, { "or": [ { "eq": {"field": {"column": "field2"}, "value": "field3"} }, { "gt": {"field": {"column": "field3"}, "value": "45"} } ]} ] } }
A couple of INSERT statements,
{ "inserts": [ { "table": {"table": "table1", "schema": "MySchema"}, "values": [ {"column": "column1", "value": "value1"}, {"column": "column2", "value": "value2"} ], "returning": [{"column": "column1", "as": "bestcolumn"}, {"column": "column2"}] }, { "table": {"table": "table2"}, "values": [ {"column": "columnA", "value": "valueA"}, {"column": "columnB", "value": "valueB"} ] } ]}
For more detailed information, see the main query modules,
An important feature of this distribution is whitelisting of allowed table and column identifiers. The whitelisting rules are defined in the format,
[ { schema => 'schemaName' || '#anySchema', <'#anyTable' || allowedTableName1 => [ '#anyColumn' || allowedFieldName1, allowedFieldName2, ... ]>, <... additional table rules ...> }, < ... additional rule sets ... > ]
and are saved in the query object when it is created. Subsequent building of the SQL statement examines this whitelist and returns an error if table/column identifiers are used that have not been explicitly allowed. This allows JSON query generation and processing to be safely separated and handled by different modules. The generating module (ex: a JavaScript client) is responsible for generating the query in stringified JSON format, and the processing module (ex: CGI script) is responsible for validating and processing that JSON query into an SQL statement.
** Important Takeaway: JsonSQL query object construction and SQL generation will fail if you have not defined any whitelisting rules. **
It is not recommended, but you can disable the whitelisting module by defining a permissive rule,
[ { schema => '#anySchema', '#anyTable' } ]
For more information on the whitelisting module, and how to construct rule sets, see the JsonSQL::Validator module.
This module is a documentation stub and does not contain any code. For detailed API information, see the appropriate modules.
For users, the main JsonSQL Query modules,
JsonSQL::Query::Select
JsonSQL::Query::Insert
For a description of the whitelisting feature,
JsonSQL::Validator
For developers,
The module used for returning errors:
JsonSQL::Error
To create a new schema,
JsonSQL::Schemas::Schema
Examples: JsonSQL::Schemas::select and JsonSQL::Schemas::insert
To create a new query object,
JsonSQL::Query::Query
If your aim is to add additional features to an existing query object, you may also need to extend the schema. If you are supporting a completely new query type, you will need to write an appropriate schema for it.
To create additional query parameters,
The individual JsonSQL::Param modules have a lot of documentation. Start with JsonSQL::Param::Tables or JsonSQL::Param::Fields to see how they work and how they integrate whitelist validation. For WHERE-like parameters, see JsonSQL::Param::Condition and JsonSQL::Param::ConditionDispatcher.
Internal Development
First public release
Minor fix to properly implement ANSI double quotes as the default for quoting identifiers.
A short list, in more-or-less relative priority, of things I would like to change/fix as time allows.
Deprecate SQL::Maker dependency.
This is only useful for SELECT queries, and there are a fair amount of bugs that need to be worked around. To strengthen feature support, a full SQL-generating backend needs to be written.
Support Common Table Expressions (CTEs) and subqueries.
Support additional query types: UPDATE, DELETE ( and maybe CREATE, DROP ).
Support database-specific drivers to better deal with database-specific nuances.
Chris Hoefler <bhoefler@draper.com>
This software is copyright (c) 2017 by Chris Hoefler.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.
To install JsonSQL, copy and paste the appropriate command in to your terminal.
cpanm
cpanm JsonSQL
CPAN shell
perl -MCPAN -e shell install JsonSQL
For more information on module installation, please visit the detailed CPAN module installation guide.