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

Paginator QueryBuilder not escaping rowcount column #12950

Closed
dnapierata opened this Issue Jul 10, 2017 · 4 comments

Comments

Projects
None yet
3 participants
@dnapierata
Contributor

dnapierata commented Jul 10, 2017

Expected and Actual Behavior

When using the paginator with a GROUP BY and HAVING clause, the sql that is generated does not escape the aliased column and thus I am receiving a syntax error from my database (MSSQL 12.0). https://github.com/phalcon/cphalcon/blob/master/phalcon/paginator/adapter/querybuilder.zep#L228

I would expect Line 228 to be

let row = db->fetchOne("SELECT COUNT(*) as \"rowcount\" FROM (" .  sql["sql"] . ") as T1", Db::FETCH_ASSOC, sql["bind"]),

Details

  • Phalcon version: (3.2.0)
  • PHP Version: (7.0.18-0ubuntu0.16.04.1)
  • Operating System: Linux
  • Installation type: installing via package manager
  • Server: Apache
  • Other related info (Database, table schema): MSSQL 12.0
@sergeyklay

This comment has been minimized.

Show comment
Hide comment
@sergeyklay

sergeyklay Jul 10, 2017

Member

Phalcon does not have build-in MSSQL dialect class. So most likely this is a new feature request for Incubator but not a Phalcon bug.

Member

sergeyklay commented Jul 10, 2017

Phalcon does not have build-in MSSQL dialect class. So most likely this is a new feature request for Incubator but not a Phalcon bug.

@dnapierata

This comment has been minimized.

Show comment
Hide comment
@dnapierata

dnapierata Jul 10, 2017

Contributor

I realize MSSQL isn't currently supported by phalcon but this fix is just to make the generated SQL more standards compliant and in fact it is already being done here https://github.com/phalcon/cphalcon/blob/master/phalcon/mvc/model.zep#L1135

I already have the dialect and adapter classes for sql server but this does not solve the issue I have posted above. I am just asking you add double quotes around rowcount so that the query builder paginator is more compatible with future db dialects.

Contributor

dnapierata commented Jul 10, 2017

I realize MSSQL isn't currently supported by phalcon but this fix is just to make the generated SQL more standards compliant and in fact it is already being done here https://github.com/phalcon/cphalcon/blob/master/phalcon/mvc/model.zep#L1135

I already have the dialect and adapter classes for sql server but this does not solve the issue I have posted above. I am just asking you add double quotes around rowcount so that the query builder paginator is more compatible with future db dialects.

@Jurigag

This comment has been minimized.

Show comment
Hide comment
@Jurigag

Jurigag Jul 10, 2017

Member

Can you do PR with fix to 3.2.x branch?

Member

Jurigag commented Jul 10, 2017

Can you do PR with fix to 3.2.x branch?

@dnapierata dnapierata referenced this issue Jul 10, 2017

Merged

escape rowcount alias #12951

2 of 3 tasks complete
@sergeyklay

This comment has been minimized.

Show comment
Hide comment
@sergeyklay

sergeyklay Jul 19, 2017

Member

Fixed in the 3.2.x branch. Feel free to open a new issue if the problem appears again. Thank you for contributing.

Member

sergeyklay commented Jul 19, 2017

Fixed in the 3.2.x branch. Feel free to open a new issue if the problem appears again. Thank you for contributing.

@sergeyklay sergeyklay closed this Jul 19, 2017

@sergeyklay sergeyklay modified the milestones: unplanned, 3.2.x Jul 19, 2017

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