Problem with queries which contain blank strings about aura.sql HOT 10 CLOSED

From what I remember about the rebuilder, it counts the number of quotes, and looks for closing sets of quotes. That might be the culprit here.

A workaround would be to use placeholders for the blank values ...

UPDATE table SET `value`=:value, `blank`= :blank, `value2` = :value2, `blank2` = :blank2, `value3`=:value3 WHERE id = :id

... and then to pass blank '' values in the array for those placeholders.

That's a workaround for the interim.

The str_replace() solution presumes that there are no occurrences of _NULL_ in other literals in the SQL string, which may not be true; in those cases, the query might be modified in unexpected ways. Probably not something that can be added to the library as-is.

However, if you can submit a failing test in a PR, I or someone else can eventually get around to working on the regex.

How does all that sound?

from aura.sql.

I was also thinking to write what @pmjones suggested passing placeholders.

from aura.sql.

Are there any tests for the regex at present? The issue is that I can't be certain what it's meant to detect or not detect as I don't understand the whole syntax at present, or what the goal is.

from aura.sql.

@M1ke I think there is no tests for regular expression itself. Don't know / remember what was the decision to extract it. But it happened in 2.1.0. iirc it was something with the place holder issues I guess.

from aura.sql.

Another workaround is to change your request and to have a newline between conditions:

UPDATE table SET 
    `value`=:value, 
    `blank`='',
    `value2` = :value2,
    `blank2` = '',
    `value3`=:value3
WHERE id = :id

This will even have the side effect of rendering you SQL query more readable. We bumped into this bug too at work and that's the workaround we choose to implement

from aura.sql.

@nyamsprod great! .

Thank you for the patch.

from aura.sql.

Is this bug not also related to #104 and #108? In other words: when merging 108, this should be fixed, right?

from aura.sql.

Note to self: add a test for this against #134 after it merges.

from aura.sql.

#134 is obviated; write a test for this against #138 .

from aura.sql.

This appears to be fixed by #138. Thanks for the report!

from aura.sql.