From Code to Community: Sponsoring The Perl and Raku Conference 2025 Learn more

 / 
JsonSQL-0.41
River stage zero No dependents
/ JsonSQL

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.