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

Already on GitHub? Sign in to your account

SQL "AS" (`.as`) should be escaped #155

Open
inossidabile opened this Issue Jan 1, 2013 · 4 comments

Comments

Projects
None yet
4 participants

Currently it does not. I tried running field.as("test.test") and it fails at both: SQLite3 and PG. It injects passed string as-is without any escaping which is wrong.

nmarley commented May 23, 2014

Are you sure this is wrong for Pg? I get errors when the aliases are quoted in Postgres:

rlist_dev=> select id, name, null as 'nothing' from users;
ERROR:  syntax error at or near "'nothing'"
LINE 1: select id, name, null as 'nothing' from users;
                                 ^

I would think that passing the alias as-is would be correct.

Owner

matthewd commented May 24, 2014

@nmarley you're using the wrong sort of quotes

nmarley commented May 24, 2014

In that case, Arel itself is generating the wrong kind of quotes. I just used the SQL generated by Arel:

rivendell:railslist nmarley$ rails c
Loading development environment (Rails 4.1.0)
>> t = User.arel_table
=> #<Arel::Table:0x007fde9d952968 @name="users", @engine=User (call 'User.connection' to establish a connection), @columns=nil, @aliases=[], @table_alias=nil, @primary_key=nil>
>> nothing = Arel::Nodes::As.new(nil, "nothing")
=> #<Arel::Nodes::As:0x007fde9d935cc8 @left=nil, @right="nothing">
>> empty_string = Arel::Nodes::As.new("", "empty_string")
=> #<Arel::Nodes::As:0x007fde9d886098 @left="", @right="empty_string">
>> ar_rel = User.select(t[:id], t[:name], nothing, empty_string);nil
=> nil
>> ar_rel.to_sql
=> "SELECT \"users\".\"id\", \"users\".\"name\", NULL AS 'nothing', '' AS 'empty_string' FROM \"users\""
>> ActiveRecord::Base.connection_config[:adapter]
=> "postgresql"

The only way that I can get this to work with Postgres is to use an Arel::Nodes::SqlLiteral, which just passes the column name unquoted.

njvack commented Sep 4, 2015

I see this in MySQL -- for example:

Arel::Table.new('foo')[:bar].as('corge grault').to_sql
=> "`foo`.`bar` AS corge grault"

The result should be:

"`foo`.`bar` AS `corge grault`"

MySQL will also allow single quotes around the alias name, but I think backticks are more correct?

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