[mrkeen]

Escaping isn't always a yes/no question.

If someone enters "foo bar" into your frontend, should the backend only see "foo%20bar" ?

[pavlov]

The back-end should see a 7-byte buffer with values [102 111 111 032 098 097 114], assume it's UTF-8 and convert that to its internal string representation?

[ezfe]

no, the backend has no reason to see `foo%20bar` - you escape when you're combining that string with other strings (ie into HTML, into a SQL query, etc.)

[evilDagmar]

There's just a whole library of CVEs for people who attempt to escape things being sent to SQL. Use parameterized queries already.

[8n4vidtmkvmk]

every one says "just use parameterized queries" but they don't handle arrays which makes the idea rather useless.

[jiggawatts]

Many database engines can handle arrays, or table-valued variables which are basically the same thing. Most ORMs will also abstract away arrays for you, so you as the developer never need to deal with escaping of data in arrays.

[8n4vidtmkvmk]

Which relational DB supports this natively?

ORMs don't count. They're just editing the SQL.

[orf]

Use one that does? Or build it yourself?

    ARRAY[?, ?, ?, ?]

[8n4vidtmkvmk]

I do, but I feel like it defeats the purpose. In order to insert those ?s you have to parse the query, which is exactly what we're trying to avoid.

[orf]

Sorry, I’m not clear on why you need to parse the query? Imagine you want to INSERT (int, array[], int) into a table.

Why can’t you just generate the following SQL?

  INSERT INTO foo VALUES (?, ARRAY[?,?,?], ?)

Where param 1-3 are indexes 0-2 in the array?

This is basically what everything else does when handing arrays, or other composite types like coordinates.

If the language or libraries are not suitable for doing this then the language or libraries are the problem, not the approach.