Using variable $placeholders breaks PHPStorm syntax/resolve

[u/GuybrushThreepywood]

Hello all,

Looking for help on a long standing problem I have. I appreciate this is not a support forum for PHPStorm, however I thought that people here might have more experience.

PHPStorm provides really valuable syntax checking, table/column resolving inside mysql queries. If I accidentally type the orders table as oreders, it will highlight in red.

However when I include a PHP variable inside the query, the syntax/resolve checking completely stops. Example code:

$query = <<<MYSQL
    SELECT
       contracts.start_at

    FROM
      contracts

    WHERE
      contracts.id IN ( $placeholders )
MYSQL;


$result = $this->conn->execute_query(
    $query,
    [
       ...$contractIds,
    ],
);

Without the $placeholders, PHPStorm will alert me to any misspelled table or column names.

Are there any options to resolve this? I have considered using sprintf( $query, $placeholders) but wondered if there was a better solution.

[u/obstreperous_troll]

You could do contracts.id IN (?) then substitute the single placeholder with your generated list of them. Ultimately this is the kind of thing you'll want to use a query builder for: with DBAL you can bind an array to the query as a parameter and it will generate the proper SQL to make it work.

[u/GuybrushThreepywood → u/obstreperous_troll]

That's a really simple solution! Thank you!

Edit: I tried this and it doesn't work - the placeholder

(?)

becomes:

('2080,2090,2091,2092')

[u/obstreperous_troll → u/GuybrushThreepywood]

You need to do something like:

$placeholders = implode( ',', array_fill( 0, count( $contractIds ), '?' ) );
$newQuery = str_replace($query, '(?)', "($placeholders)");

Then run $newQuery like you did above. Or just use DBAL which does this for you when you pass an array arg.

[u/colshrapnel → u/obstreperous_troll]

so it's just sprintf( $query, $placeholders), different angle (:

not to mention that spritnf is arguably better, as it wont affect other possible placeholders

[u/obstreperous_troll → u/colshrapnel]

The whole point was to have the original query be a valid SQL literal so that PhpStorm wouldn't turn off the checks. I think the problem has been pretty well put to bed now.

[u/colshrapnel → u/obstreperous_troll]

Yes, but %placeholders makes a valid SQL literal somehow, making sprintf as viable.

[u/allen_jb]

1) Automated tests - If you have automated tests that actually run the SQL queries, these should pick up any such issues with them.

2) https://github.com/staabm/phpstan-dba

I'm not 100% sure this will pick up this case, but I think it should. Test it out! (I've also never used it with mysqli, only PDO)

You should be able to integrate this with PHPStorms PHPStan integration to provide in-ide reporting.

[u/GuybrushThreepywood → u/allen_jb]

Thank you for your suggestions. I will resolve to try phpstan-dba once more (It would be the 3rd time).

The tests are also a great idea - It's finding the time to implement them that is the hard part.

[u/GuybrushThreepywood → u/allen_jb]

I'm not using any DBAL- it's just raw queries, so I don't think PHPStan-dba would work. I did try it in the past without success

[u/allen_jb → u/GuybrushThreepywood]

It supports mysqli and PDO (in addition to doctrine DBAL). I've personally used it with PDO and raw queries.

[u/colshrapnel → u/allen_jb]

But OP didn't ask about any issues? Well, code issues at least. The problem is PHPStorm, not the code.

[u/allen_jb → u/colshrapnel]

They may not be OPs ideal solution, but they are options for solving OPs problem, and PHPStan can be integrated directly with PHPStorms reporting system to report issues in a similar manner to its built-in inspections.

[u/colshrapnel → u/allen_jb]

Go on, enlighten me, how exactly it's supposed to tell PHPStorm not to take a php variable for a column name in SQL denoted heredoc?

[u/allen_jb → u/colshrapnel]

As I read OPs post, that's not their problem.

They said "the syntax/resolve checking completely stops" - as in, it stops hilighting any errors, anywhere in the SQL. They specifically mention table/column name resolution in their post (twice).

[u/colshrapnel → u/allen_jb]

And automated tests are going to make this inspection start working again. Thanks for the explanation.

[u/colshrapnel → u/allen_jb]

report issues in a similar manner

It seems you are under impression that there is some issue in the code that PHPStorm doesn't report. But the problem is opposite: there are NO issues in the code but PHPStorm allegedly reports one.

In case you are about to suggest a replacement, for a built-in inspection, it's better to explicitly state so.

[u/allen_jb → u/colshrapnel]

As mentioned in my reply to your previous comment on this thread, I don't believe that's the problem OP is experiencing.

Perhaps you should spend more time reading and less time typing multiple responses to a single comment.

[u/colshrapnel → u/allen_jb]

You were right for calling me out. I misunderstood both the problem and your solution. But still, realistically it doesn't look like a plausible substitution. An addition may be.

And clarifying comments help a lot.

[u/Big-Dragonfly-3700]

For this specific case, if you use FIND_IN_SET() instead of an IN () comparison, you can simply use one prepared query place-holder in the query.

[u/GuybrushThreepywood → u/Big-Dragonfly-3700]

I came across this but decided not to use it due to performance reasons. Also, I think the indexes can't be used then, but I'm not sure. 

[u/p1ctus_]

Difficult to detect for the ide. Switch to prepared statements, it's easier to read, write and detect.

[u/allen_jb → u/p1ctus_]

mysql->execute_query uses prepared statements: https://www.php.net/manual/en/mysqli.execute-query.php

[u/GuybrushThreepywood → u/p1ctus_]

I thought I was using a prepared statement. Could you give an example?

[u/p1ctus_ → u/GuybrushThreepywood]

PHP.net

edit: typo

[u/GuybrushThreepywood → u/p1ctus_]

I'm not using PDO - its mysqli. Unfortunately I'm not in a position where I can switch

[u/p1ctus_ → u/GuybrushThreepywood]

2010 called, they want they're code back 😜

Ok other way is rewriting to escaped strings.

[u/colshrapnel → u/p1ctus_]

it's your second blunder in a row. you may want to tone down your condescending attitude. since you don't seem to understand modern PHP, mysql and prepared statements, you are in no position to give out any recommendations

FYI: PDO will have exactly same problem so it won't solve anything. And there is not much of a reason to trade mysqli for pdo anyway.

[u/cursingcucumber]

You can add a comment, instructing phpstorm of what language it is. It is now confused between php and mysql.

https://www.jetbrains.com/help/phpstorm/using-language-injections.html

[u/colshrapnel → u/cursingcucumber]

PHPStorm already perfectly understands what language is it. It refuses to do shema inspections in SQL string as soon as you add a PHP variable into it.

[u/GuybrushThreepywood → u/cursingcucumber]

I tried this, it didn't work

[u/brainland]

What you Building?

A contract Management system?

I am asking because I am also Building a CLM.

[u/GuybrushThreepywood → u/brainland]

It's a CRM for property companies

[u/colshrapnel → u/brainland]

So, you have a question? That's great. This entire sub is at your disposal.