0

In PHP when using PDO to query my PostgreSQL database I have this query

$stmt = $myPDO->prepare(
    "SELECT clientid,status,action,datecompleted FROM clientapi.table1"
);
$stmt->execute();

As you can see, the database uses several reserved words as column names, status & action. Should I be escaping these with backticks? And if yes, should I only escape the reserved words? Lastly this query does already run fine without any issues without escaping them

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
code-is-life
  • 175
  • 2
  • 16
  • 1
    In that case I'd say IMO the question ought to be more like _could_ I then _should_ I... if it works without them then clearly you don't need to. P.S. This really has nothing to do with PHP or PDO...the SQL syntax requirements would remain the same no matter what client library or tool you're using to run it. – ADyson May 17 '22 at 08:06
  • @ADyson I am asking the question to find out what the correct way is? Yes it works without escaping but I want to know what the right way is? And as it does work why is it recommended to escape them using double quotes as per the linked question above? Thanks for the last point you mentioned, where its not about the library used but in fact just SQL instead – code-is-life May 17 '22 at 08:28
  • 3
    Reserved keywords are escaped using double quotes in SQL. Those dreaded backticks are invalid in an identifier in SQL. See [the manual](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) for details –  May 17 '22 at 08:33
  • 1
    @code-is-life you'd have to ask the designers of the language why it still works like that. "correct" is somewhat subjective - if something works and meets all your requirements then arguably it's correct _for you_, but may not be correct for someone else. It's probably recommended to do it with quotes to avoid any ambiguity or future issues if the language syntax rules are made stricter. Keep in mind though that there can be downsides to liberally spraying quote-marks everywhere - see https://www.prisma.io/dataguide/postgresql/short-guides/quoting-rules#double-quotes. So it's really up to you – ADyson May 17 '22 at 08:45
  • @ADyson thanks perfectly explained and definitely provides me with enough to move forward with – code-is-life May 17 '22 at 09:10
  • Just to add here, enclosing the reserved words in double quote presents a syntax error, unexpected 'status' (T_STRING), expecting ')' – code-is-life May 19 '22 at 08:10

0 Answers0