Skip to content
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

Ability to see SQL generated statement with parameters substituted #568

Closed
1 task done
thinkingmedia opened this issue Dec 21, 2017 · 9 comments
Closed
1 task done
Assignees

Comments

@thinkingmedia
Copy link
Contributor

From @brettl on June 11, 2017 19:47

  • enhancement

  • CakePHP Version: 3.next
    When running the ->sql() function on an ORM call allow the ability to see the sql statement with the parameters in the place holders.

Currently when calling sql on the ORM you will see example "where id = :c1"
allow the ability to call an example ->sql(1) or ->sql()->substituted(); and the resulting sql will now be "where id = 1"
this would allow the ability to take your sql being generated and run in a sql viewer /editor and run your command and possibly see if you are producing the incorrect data or if you are generating the incorrectsql statement

Copied from original issue: cakephp/cakephp#10761

@thinkingmedia
Copy link
Contributor Author

From @dereuromark on June 11, 2017 20:19

Isn't (string)...->sql() casting doing this? Showing you the final SQL query that would be run?

@thinkingmedia
Copy link
Contributor Author

From @markstory on June 12, 2017 5:17

@dereuromark Right now there isn't a way to get the SQL query that will be executed with placeholders populated from the database layer. You can only get that information by interpolating the parameters yourself, or using a tool like debug kit.

We won't be able to chain off of sql(), but we could have a new method that returns the populated SQL query.

@thinkingmedia
Copy link
Contributor Author

From @makallio85 on June 26, 2017 5:27

+1 to this

@thinkingmedia
Copy link
Contributor Author

From @lorenzo on June 29, 2017 13:45

I'd like to do this for when calling debug($query)

@thinkingmedia
Copy link
Contributor Author

Oh I would like to work on this. I have a working example already and just need to migrate it to the debugKit.

Always including the variable bindings is risky. For example; some of the IN operators can be huge which makes reading the SQL difficult. So I will make this an optional feature.

@elovin
Copy link

elovin commented Jun 5, 2019

Is this only possible with the debug kit in debug mode? I would like to use cakephp orm to build the sql query and then send the full sql string as a async database query with another library (in my case reactphp MySQL). To do this I would need the full sql query string including params, is that possible?

@markstory
Copy link
Member

markstory commented Jun 5, 2019

No that isn't possible. Inserting the parameters into a SQL string defeats prepared statements which is one of the main ways CakePHP prevents SQL injection. You might be able to serialize and deserialize a query object depending on parameter values and attached processing functions instead.

@elovin
Copy link

elovin commented Jun 6, 2019

Yes of course you are right, my question was ill formed.

What I need is to get the params from the cakephp query so that I can use them in the prepared statement of the async db query.

I realized that that's already possible if I call $query->getValueBinder()->bindings(); after calling $query->sql(); and get the values from the resulting array.

I then replace the cakephp placeholders with the placeholder from reactphp mysql which is using ? as a placeholder. mb_ereg_replace("(?<=\s):c[0-9]+(?=\s|$|\)|\])", '?', $sql)

Question:

Can I expect the cakephp placeholders in the sql string to always match the order of the params array even if I use subqueries ?
E.g. SELECT ... c:0 ... c:1 ... in the string and [c:0 => [..], c:1 => [..]] in the params array even when using subqueries in matching, contain ... (I tested this with some queries so this seems to be the case) ?

@markstory
Copy link
Member

Can I expect the cakephp placeholders in the sql string to always match the order of the params array even if I use subqueries ?

Generally yes. However, you're going into undocumented and generally untested areas of behavior so I won't make any strong promises.

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

No branches or pull requests

5 participants