New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can't use alias with SELECT #34

Closed
jasonl1 opened this Issue Jul 19, 2017 · 7 comments

Comments

2 participants
@jasonl1

jasonl1 commented Jul 19, 2017

I used to be able to use alias' with SELECT() on 2.8.3, but on the latest version I get an SQL error. For example:

  1. $db->select('o_fname AS firstName','active','','','','10');
  2. $db->query('SELECT o_fname AS firstName FROM active LIMIT 10');

Both used to work in 2.8.3 but now 1 fails because SELECT escapes the column (including the alias). Do I have to rewrite my queries or could this be addressed in the future?
screen shot 2017-07-19 at 3 38 33 pm

@stefangabos

This comment has been minimized.

Show comment
Hide comment
@stefangabos

stefangabos Jul 19, 2017

Owner

Since in a SELECT statement there can be pretty complex stuff, I prefer to leave it like that, instead of writing code to detect what it should escape and what not. The select method was intended for very simple statements (although, I do realise that using AS still counts as being simple) and never thought that people would start and abuse it 😄 I had lots of requests regarding the method and is the one method from this library that I never, ever used it myself. So, yes, I would recommend rewriting your queries (if that is possible). Other than that, I could maybe add a method/property to disable escaping for the select method.

Owner

stefangabos commented Jul 19, 2017

Since in a SELECT statement there can be pretty complex stuff, I prefer to leave it like that, instead of writing code to detect what it should escape and what not. The select method was intended for very simple statements (although, I do realise that using AS still counts as being simple) and never thought that people would start and abuse it 😄 I had lots of requests regarding the method and is the one method from this library that I never, ever used it myself. So, yes, I would recommend rewriting your queries (if that is possible). Other than that, I could maybe add a method/property to disable escaping for the select method.

@jasonl1

This comment has been minimized.

Show comment
Hide comment
@jasonl1

jasonl1 Jul 20, 2017

Thanks Stefan. What i ended up doing was adding a check for the 'AS' alias in the function _escape
` private function _escape($entries) {

    // treat argument as an array
    $entries = (array)$entries;

    $result = array();

    // iterate over the entries given as argument
    foreach ($entries as $entry) {

        // explode string by dots
        $entry = explode('.', $entry);

        // iterate over the segments
        $entry = array_map(function($value) {

            // trim ticks and whitespace
            $value = trim(trim($value, '`'));

            // if not * or a MySQL function
            if ($value !== '*' && !$this->_is_mysql_function($value))
                // escape for alias
                if(stripos($value, " as " )){
                    $v = explode(" as ",str_replace(" AS ", " as ",$value));
                    // enclose value in grave accents
                    return '`' . $v[0] . '` AS ' . $v[1];
                }
                // enclose value in grave accents
                return '`' . $value . '`';

            // return the value as it is otherwise
            return $value;

        }, $entry);

        // concatenate the string back and add it to the result
        $result[] = implode('.', $entry);

    }

    // recompose the string and return it
    return implode(', ', $result);

}`

jasonl1 commented Jul 20, 2017

Thanks Stefan. What i ended up doing was adding a check for the 'AS' alias in the function _escape
` private function _escape($entries) {

    // treat argument as an array
    $entries = (array)$entries;

    $result = array();

    // iterate over the entries given as argument
    foreach ($entries as $entry) {

        // explode string by dots
        $entry = explode('.', $entry);

        // iterate over the segments
        $entry = array_map(function($value) {

            // trim ticks and whitespace
            $value = trim(trim($value, '`'));

            // if not * or a MySQL function
            if ($value !== '*' && !$this->_is_mysql_function($value))
                // escape for alias
                if(stripos($value, " as " )){
                    $v = explode(" as ",str_replace(" AS ", " as ",$value));
                    // enclose value in grave accents
                    return '`' . $v[0] . '` AS ' . $v[1];
                }
                // enclose value in grave accents
                return '`' . $value . '`';

            // return the value as it is otherwise
            return $value;

        }, $entry);

        // concatenate the string back and add it to the result
        $result[] = implode('.', $entry);

    }

    // recompose the string and return it
    return implode(', ', $result);

}`
@stefangabos

This comment has been minimized.

Show comment
Hide comment
@stefangabos

stefangabos Jul 20, 2017

Owner

Ok, I've implemented your suggestion 😄
Thanks!

Owner

stefangabos commented Jul 20, 2017

Ok, I've implemented your suggestion 😄
Thanks!

@jasonl1

This comment has been minimized.

Show comment
Hide comment
@jasonl1

jasonl1 Jul 20, 2017

thanks Stefan, looks like it may need a bit more work, if there is an SQL function with the alias it'll fail - i.e.; SELECTsum(amount) as amount

jasonl1 commented Jul 20, 2017

thanks Stefan, looks like it may need a bit more work, if there is an SQL function with the alias it'll fail - i.e.; SELECTsum(amount) as amount

@stefangabos

This comment has been minimized.

Show comment
Hide comment
@stefangabos

stefangabos Jul 20, 2017

Owner

if there's an SQL function anywhere, everything is returned as it is because of the call to _is_mysql_function at line 4492

Owner

stefangabos commented Jul 20, 2017

if there's an SQL function anywhere, everything is returned as it is because of the call to _is_mysql_function at line 4492

@jasonl1

This comment has been minimized.

Show comment
Hide comment
@jasonl1

jasonl1 Jul 20, 2017

i can see why the select is just for simple queries - many cases to consider, like nested functions. Great job on the plugin still!

jasonl1 commented Jul 20, 2017

i can see why the select is just for simple queries - many cases to consider, like nested functions. Great job on the plugin still!

@stefangabos

This comment has been minimized.

Show comment
Hide comment
@stefangabos

stefangabos Jul 20, 2017

Owner
Owner

stefangabos commented Jul 20, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment