So, yet another headscratcher / looking for best practice… How to best sanitize values before sending them to a database:
Option 1) use prepared statements - which takes care of escaping single quotes for instance, but which doesn’t appear to work with a datePicker’s selectedDate (-> ERROR: column “due” is of type timestamp without time zone but expression is of type character varying), besides, prepared statements are discouraged in certain situations anyway…
Option 2) don’t use prepared statements - and handle all the escaping etc. in code - but where? wrap every query call in a JS function and pass to be escaped values as params? apparently a .replace( … ) insides a {{ }} block of a query doesn’t work (event if it’s offered in command completion ?)
One more comment in something which has cost me some time:
The following Regex syntax in an SQL query doesn’t work (now idea how to escape the ’ to make it work): (the objective here is to clean ’ chars from multiline text)
Hi @arminus, please don’t try to escape characters this way for two major reasons:
This protection is executing on the client system because it’s JavaScript. That means that your users (or attackers) can modify or disable the protection entirely. They can also intercept the HTTP traffic trivially and modify the requests to include attacks. Client side validation must not be your only defense–it should just be used to for convenience to alert regular users of invalid input.
What are you doing is creating a blocklist (of just single quote) which will be escaped upon execution. However, this almost always breaks down and allows injection due to other characters and character sets. Instead, create an allowlist for what characters are ok, and escape everything else. The more you precise you can be (for example, a US zip code only has 0-9 and is exactly six digits) the safer it will be. But as said in bullet 1, this should be server side.
You want the validation on the server because you cannot guarantee that an attack won’t come in from a client, and it also means attackers can’t (at all, or at least easily) go for SQL Injection.
Your safest option is prepared statements so there’s no opportunities for injection.
Hi @sullo thanks for pointing this out, I am aware of that, been around the block for a couple of years In my current case, I’m prototyping an app for internal use, so I’m not really concerned about these aspects.
In my current case, I’m prototyping an app for internal use, so I’m not really concerned about these aspects.