Validate An Object If Contains SQL Statements In Its Fields

- 1 answer

I have the following structure of an object queryParams:

export default interface Query {
    select: SelectParam[];
    where?: WhereParam[];
    orderBy?: OrderByParam[];
export interface SelectParam {
    field: string;
export interface WhereParam {
    conditions: ConditionsParam[];
    operator?: string;
export interface ConditionsParam {
    prop: string;
export interface OrderByParam {
    prop: string;

I need to validate its fields, even the nested ones or the ones that are arrays of object, if it contains an SQL injected statements into them.

I'm doing the validation this way, which I think that it's not efficient nor dynamic .. is there a better way to do that?

const res = =>
            (i.aggregator ? ((i.aggregator.includes('select') && i.aggregator.includes('from')) || i.aggregator.includes('update') || i.aggregator.includes('drop') || i.aggregator.includes('delete')) : false) || // aggregator CRUD
            ((i.field.includes('select') && i.field.includes('from')) || i.field.includes('update') || i.field.includes('drop') || i.field.includes('delete')) || // field CRUD
            (i.type ? ((i.type.includes('select') && i.type.includes('from')) || i.type.includes('update') || i.type.includes('drop') || i.type.includes('delete')) : false) // type CRUD
// the same thing for the rest of the fields.

I believe using:

Object.entries(queryParam).forEach(([key, value]) => {

is one of the ways to do it, but not sure how to use it as I'm new to Javascript/Typescript world.



Any time data needs to be validated/cleaned, I would strongly recommend using a third party library. In this case, you may want to take a look at Joi ( that has a very robust set of rules and is very flexible should you need to change your logic in the future

The way you typically work with Joi, you would start with defining the "schemas" (which is Joi language for "validation rules") working your way from the "leaf" objects and up until you get to the root. Your code might look like the following:

const prohibitedTerms = ['select', 'from', 'yellow cat']

const orderBySchema = Joi.string().required().invalid(prohibitedTerms)
const conditionsParamSchema = Joi.string().required().invalid(prohibitedTerms)
const selectParamSchema = Joi.string().required().invalid(prohibitedTerms)

const whereParamSchema = Joi.object({
  conditions: Joi.array().items(conditionsParamSchema).required(),
  operator: Joi.string().optional

const querySchema = Joi.object({
  select: Joi.array().items(selectParamSchema).required(),
  where: Joi.array().items(whereParamSchema).optional(),
  orderBy: Joi.array().items(orderBySchema).optional()


const myData = <something that needs to be validated>
const { error, value } = querySchema.validate(myData)
if (error) {
  // Raise alarm about invalid input, possible injection etc. 

You probably have noticed repetitive Joi.string().required().invalid(prohibitedTerms) - this is done just for illustration, normally this type of repetitive schema definition would be declared as a common constant and reused.

At first, it may seem that this approach is far more verbose than the "straight" validation by hand and requires more work than it should. In reality, separating validation rules from the actual code makes it far more flexible and maintainable solution - and far more readable, too. Normally the schemas would be defined in their own shared module and imported whenever the data needs to be checked.

Hope that helps!