Re: PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 9:15 AM, Ray Cote <rgacote(at)appropriatesolutions(dot)com>
wrote:

> On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net
> > 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.