Hash in SQL string breaks syntax highlighting

[roblourens]

From microsoft/vscode#48096

Steps to Reproduce:

This code:

<?php
$sql = 'SELECT \' #\' || fieldid FROM sometable';

Highlights like so:

I guess this is a comment in SQL, but it seems hard to tell that it's actually inside a string.

[neerolyte]

I guess this is a comment in SQL, but it seems hard to tell that it's actually inside a string.

Sorry I probably should have explained more what that exactly is doing for anyone not familiar enough with SQL. The hash is actually just a string literal hash, it has no special significance at this point in SQL as it's within single quotes.

In PHP we're constructing the literal string:

SELECT ' #' || fieldid FROM sometable

which VSCode/Atom are correctly guessing is SQL (based on the SELECT?).

The ' # is constructing a literal string to be concatenated (|| is the SQL concatenation operator) with the actual row value of fieldid.

So if we have a single row in sometable with 1 as the fieldid the query would end up returning #1 instead of just 1 in this case.

A little oddball I recognise, but I cut it down from a bigger snippet I hit in code that I was reviewing:

$sql = 'SELECT v.assetid, a.name || \' #\' || v.fieldid as name, v.value
    FROM sq_ast_mdata_val v, sq_ast a WHERE a.assetid = v.fieldid AND ';

In this case a.name and v.fieldid are concatenated together with # so that for a name of foo and a fieldid of 123 you end up with the DB directly returning foo #123 as the value.

[Arcanemagus]

Thanks for taking the time to contribute!

We noticed that this is a duplicate of #321. You may want to subscribe there for updates.

Because we treat our issues list as the Atom team's backlog, we close duplicates to focus our work and not have to touch the same chunk of code for the same reason multiple times. This is also why we may mark something as duplicate that isn't an exact duplicate but is closely related.

For information on how to use GitHub's search feature to find out if something is a duplicate before filing, see the How Can I Contribute? section of the Atom CONTRIBUTING guide.

[neerolyte]

I don't think that #321 is the same issue, that issue describes the quote spilling out past the end of the string and appears to be ok in the VSCode I'm currently running, e.g compare this highlighting:

[Arcanemagus]

The root cause is the same: The sub-language (SQL) isn't terminating correctly, fixing the problem should fix this case as well.

[neerolyte]

But in this case the sub-language SQL is actually terminating at the final quote.

[Arcanemagus]

My apologies, at first glance they looked like the same thing, but this actually does look like a separate issue.

It looks like this is a bug in the SQL sub-language where it isn't properly detecting that this is within a string. I'm guessing that it's because it's seeing the \' literally, instead of parsing it as just a ', so it doesn't actually think that # is within a string, since it is marking the rest of the line with comment.line.number-sign.sql.

Leading credence to this theory is the fact that if you change the PHP string to be ", everything works as expected.

So this code highlights correctly:

<?php
$sql = "SELECT ' #' FROM sometable";

Confirming this on Atom v1.27.0-beta0, which is using [email protected].