The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.

NAME

JsonSQL - JsonSQL distribution. A collection of modules for generating safe SQL from JSON strings.

VERSION

version 0.41

SYNOPSIS

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.

DESCRIPTION

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,

JsonSQL::Query::Select for SELECT statements
JsonSQL::Query::Insert for INSERT statements

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.

METHODS

Methods

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,

For a description of the whitelisting feature,

For developers,

Changes

0.1 - 0.3

Internal Development

0.4

First public release

0.41

Minor fix to properly implement ANSI double quotes as the default for quoting identifiers.

TODO

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.

AUTHOR

Chris Hoefler <bhoefler@draper.com>

COPYRIGHT AND LICENSE

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.