Re: PostgreSQL Developer Best Practices

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Mon, Aug 24, 2015 at 9:15 AM, Ray Cote <rgacote@xxxxxxxxxxxxxxxxxxxxxxxx> wrote:

On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert <Karsten.Hilbert@xxxxxxx> wrote:

> 1. Prefix ALL literals with an Escape
>    EG:  SELECT E'This is a \'quoted literal \'';
>         SELECT E'This is an unquoted literal';
>
>    Doing so will prevent the annoying "WARNING:  nonstandard use of escape in a string literal"

I'd be concerned that what is missing here is the bigger issue of  Best Practice #0: Use Bound Variables. 

The only way I've seen invalid literals show up in SQL queries is through the dynamic generation of SQL Statements vs. using bound variables. 

Not using bound variables is your doorway to SQL injection exploits.

​SELECT * FROM joblist WHERE job_status = 'Active';  is not at risk of an exploit...but your point is still a good one.

The other area where this is likely to crop up is in using regular expressions.  From that use case alone I've learned to only use E'' when I need the escaping behavior of the blackslash.  Since you rare need that when constructing a regexp I would rare write a regexp literal using E''.

David J.