[SECURITY] OWASP SQL Injections. Experiences endpoint

[davorpa]

Prepared statements should be used prior escape values. There are a high risk of SQL injection if raw values direct from request parameters/body are provided

• Like clauses value not escaped: searchExp / searchCity https://github.com/vanexperiences/proyecto_experiencias/blob/4c937e2c728278c81343e1608391e54c4e137def/backend/controllers/experiences/getAllExperiences.js#L27 https://github.com/vanexperiences/proyecto_experiencias/blob/4c937e2c728278c81343e1608391e54c4e137def/backend/controllers/experiences/getAllExperiences.js#L34

• Sql where clause not escaped: cityFilter categoryFilter disp dateStart dateEnd https://github.com/vanexperiences/proyecto_experiencias/blob/4c937e2c728278c81343e1608391e54c4e137def/backend/controllers/experiences/getAllExperiences.js#L83 https://github.com/vanexperiences/proyecto_experiencias/blob/4c937e2c728278c81343e1608391e54c4e137def/backend/controllers/experiences/getAllExperiences.js#L111 https://github.com/vanexperiences/proyecto_experiencias/blob/4c937e2c728278c81343e1608391e54c4e137def/backend/controllers/experiences/getAllExperiences.js#L127 https://github.com/vanexperiences/proyecto_experiencias/blob/4c937e2c728278c81343e1608391e54c4e137def/backend/controllers/experiences/getAllExperiences.js#L131-L132

[hatashi199]

How can i solve this?? I don't have idea.

[davorpa]

How can i solve this?? I don't have idea.

Both mysql2 and mysql node drivers are compatible so... reading mysql driver documentation it's possible escape values using one of mysql.escape(), connection.escape() or pool.escape() utility methods. Take also a look at mysql2 docs.

Or use prepared statements same as is done in other queries. A template...

SELECT * from foo WHERE  bar LIKE concat('%', ?, '%')

Ref: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat

About dates. Date objects are accepted directly by driver. It's posible parse from string to date using Date(). If provided string/value is not compatible, internal value is NaN.

function isValidDate(d) {
    return Object.prototype.toString.call(d) === "[object Date]" && !isNaN(d.getTime());
]

Date.prototype.isValid = function () {
  // If the date object is invalid it
  // will return 'NaN' on getTime() 
  // and NaN is never equal to itself.
  return this.getTime() === this.getTime();
};

let d = new Date("This is not date.");
d.isValid();                 // false
isValidDate(d);              // false
isValidDate(1);              // false
isValidDate('2021-08-07');   // false

Both aproaches are valid: with isNaN() or check getTime().

I hope it helps :hugs:

[vanexperiences]

Nah, give me a syntax error near ? in each filter

[davorpa]

Nah, give me a syntax error near ? in each filter

Use escape instead. If I have a bit time, I'll prepare a PR to fix it. The rest of endpoints implemented later are out of my scope