Embedded SQL: string.quoted.double.sql.php with a single quote causes SQL scope to continue

[alexr00]

Originally from @MmAaXx500 in microsoft/vscode#97703

Prerequisites

• Put an X between the brackets on this line if you have done all of the following:
  • Reproduced the problem in Safe Mode: https://flight-manual.atom.io/hacking-atom/sections/debugging/#using-safe-mode
  • Followed all applicable steps in the debugging guide: https://flight-manual.atom.io/hacking-atom/sections/debugging/
  • Checked the FAQs on the message board for common solutions: https://discuss.atom.io/c/faq
  • Checked that your issue isn't already filed: https://github.com/issues?utf8=✓&q=is%3Aissue+user%3Aatom
  • Checked that there is not already an Atom package that provides the described functionality: https://atom.io/packages

Description

When there's a single quote inside a string.quoted.double.sql.php scope, the SQL scopes don't stope at the next double quote.

Steps to Reproduce

<?php
$wrong    = "SELECT * FROM '" . $var["foo"] . "'";
$expected = "select * from '" . $var["foo"] . "'";
?>

2. In the $wrong if you delete the first single quote, you can see that the scopes then match $expected.

https://user-images.githubusercontent.com/36025825/81802535-8caa1f80-9516-11ea-8d1e-3a0d83038693.png

Versions

Version 0.44.4, from commit 882f6c0

[byronigoe]

Same issue, different example.

$insert = "INSERT INTO todo (app, user_id, created, number) VALUES ('" . 
	$app . "', '" . $user_id . "', now(), '";

Note how the syntax highlighting is wonky for the next couple lines.

[KapitanOczywisty]

These are known issues and rather hard to fix in current implementation. PR: #359 Issues: #348 #336 #333 and probably more

However these problems could be avoided in many cases. @byronigoe In your code I would suggest using prepared statements:

$insert = $conn->prepare("INSERT INTO todo (app, user_id, created, number)
VALUES ('{$app}', '{$user_id}', now(), ?)");
foreach($newtodo as $number){
	$q2 = $insert->bind_param('s', $number);
	if(!$q2){
		returnError(/*......*/);
	}
}

[byronigoe]

Thanks @KapitanOczywisty. Prepared statements are better for other reasons too. FWIW, I vote in favor of #359

[sogerc1]

I think I have the same issue:
$sql .= " AND id_cf <> all(array[".implode(',', $enabled['cf'])."])";

In this simpler expression the SQL scope does not continue, only the implode() call is considered part of the string:
$sql .= " AND id_cf <> all(array[".implode(',', $enabled)."])";