Force escape column names in Postgresql

Force escape column names in Postgresql

Juninho De LucaJuninho De Luca Posts: 7Questions: 2Answers: 0
edited September 2019 in Editor

Hi guys (Allan!),
I'm moving my system to Heroku, which uses mainly Postgres (other option is ClearDB/Mysql 5.5 based, very old). So... I've created the migration tables with Laravel and seeded it. Voilà! DB is populated. But, lasts 2 days I have working fixing a lot of important errors. As I was using MySQL, some of my tables have columns names as start, end, group. That are reserved words in Postgres, but not in MySQL. In the PG query, I just can use something like INSERT INTO (id, "group", "end" ...) to escape the column name to make it work. But Editor looks don't do that. And I have some dozens of tables in this situation to change and test everything (and change controllers, javascript, etc). Theres some place or setting where I can add something like '"' . $field . '"' for escape all columns?
Sorry my bad english.

Answers

  • allanallan Posts: 51,440Questions: 1Answers: 7,758 Site admin

    Just to confirm - this is with PHP? You should be able to replace this line with:

    protected $_identifier_limiter = array( '"', '"' );
    

    And I believe that should do it.

    Allan

  • Juninho De LucaJuninho De Luca Posts: 7Questions: 2Answers: 0
    edited September 2019

    I tryed, but seems don't work. I suspect was because I ever use compound field names like Field::inst( 'projects.description' ) instead of Field::inst( 'description' ). But not sure. Anyway, I just found a "forced" solution, but not sure if will pass in future tests hehe

    In Database\Query.php, I changed the return of _build_field() method like below

    //return ' ' . implode( ', ', $a ) . ' '; <<<< ORIGINAL!!!
    return ' "' . implode( '", "', $a ) . '" ';
    

    This is an exemple of the output string

    INSERT INTO  projects  ( "status", "closed", "label_id", "name", "start", "end" ) VALUES (  :status,  :closed,  :label_id,  :name,  :start,  :end )
    

    Now it works fine.

  • allanallan Posts: 51,440Questions: 1Answers: 7,758 Site admin

    \Interesting - thanks for that. This is something I need to look into and I've made a note to that effect (DD-1097 in our internal bug tracker).

    Allan

  • Juninho De LucaJuninho De Luca Posts: 7Questions: 2Answers: 0

    In fact, I rolled back. That works fine to Editor in POST requests, but not for GET (some issue with count). I wasn't able to finish the migration due a lot of problems with postgres (not datatables issues). So... I changed my add-on to Jaws-DB (mariaDB) and now it's working again.

Sign In or Register to comment.