Is it possible to validate JSON on the PostgreSQL side?
Writing APIs I used to validate all input parameters on the Java (or PHP, whatever) side, but now we have moved our databases to PostgreSQL, which gives us great JSON functions like plotting JSON from table rows and more (I didn't find anything we can't work with without PGSQL JSON functions). So I thought, what if I do all Postgres parameter checks (also considering that I can return JSON directly from the database)?
In Java, I did it like this:
if (!params.has("signature"))
//params comes from @RequestBody casted to JSONObject
return errGenerator.genErrorResponse("e01"); //this also need database access to get error description
In Postgres, I would do it like this (tested, works as expected):
CREATE OR REPLACE FUNCTION test.testFunc(_object JSON)
RETURNS TABLE(result JSON) AS
$$
BEGIN
IF (_object -> 'signature') IS NULL --so needed param is empty
THEN
RETURN QUERY (SELECT row_to_json(errors)
FROM errors
WHERE errcode = 'e01');
ELSE --everything is okay
RETURN QUERY (SELECT row_to_json(other_table)
FROM other_table);
END IF;
END;
$$
LANGUAGE 'plpgsql';
Etc...
The only problem I see so far is that if we go to MS SQL or Sybase it will have to rewrite all procedures. But as NoSQL comes in more and more, it seems unlikely, and if we move to a NoSQL database we also have to recode all the APIs
source to share
There are mainly two elements to consider:
-
The closer you put your checks in the data warehouse, the safer. If you have a database doing all the checks, they will run regardless of how you interact with it, whether through an application or using some third party tool that you can use (at least for maintenance). In this sense, database-side validation improves security (as in "data consistency"). In this regard, it does its best to have the database perform checks.
-
The closer you place your checks to the user, the faster you can respond to their input. If you have a web application that needs fast response times , you probably want to do client side validation.
And consider the important:
- You may also need to consider your team knowledge : the more comfortable developers are. If you know your Java library much better than you know your database functions ... it might make sense to do all the checks from the Java side.
You might have a third way: do both checks sequentially , from the application side (client) and then from the base (server). Unless you have complex automation, it takes extra work to ensure that all the checks performed are consistent. That is, there should not be any client-side locked data that should be allowed when checked against the database. At least the most basic checks are done in the early stages, and all of them (even if they are redundant) are performed in the database.
If you can afford to move data across multiple layers of the application, I would go with security. However, the choice to be made depends on the specific case.
source to share