In writing a test I was paring strings. And the test came back as Failing. I manually copy pasted the string and it works... Note the mysql string syntax; but it never touches mysql up to this point.

Console.logged copy+paste Both Strings look like this:

console.log(replaced);
"SELECT COUNT(*) FROM interaction WHERE ambassador_name LIKE '%' AND influencer_name = ?"

console.log(sqlQuery0);
"SELECT COUNT(*) FROM interaction WHERE ambassador_name LIKE '%' AND influencer_name = ?"

It Shouldn't be failing but it does. So I wanted to see where it fails:

it( "submits the proper first sql query", function(){
  var replaced = dao.SQLquery[0].replace(/  +/g, ' ');
  for (var i = 0; i < replaced.length; i++) {
    if (replaced[i] != sqlQuery0[i] ){
      console.log(replaced.slice(i-10,i+10));
      console.log(sqlQuery0.slice(i-10,i+10);
      break
    }
  }
})

TO My SUPRISE THE ABOVE RETURNED this..

me LIKE '%' AND inf
me LIKE '%' AND infl

They can't be different lengths.... right. I tried again.

  it( "submits the proper first sql query", function(){
    var replaced = dao.SQLquery[0].replace(/  +/g, ' ');
    for (var i = 0; i < replaced.length; i++) {
      if (replaced[i] != sqlQuery0[i] ){
        console.log('|'+ replaced.slice(i-10,i+10)+'|',replaced.slice(i-10,i+10).length);
        console.log('|'+sqlQuery0.slice(i-10,i+10)+'|',sqlQuery0.slice(i-10,i+10).length);
        break
      }
    }
  })

And to my bigger suprise:

|me LIKE '%' AND inf| 20
|me LIKE '%' AND infl| 20

But the First string is only length 19!

My last attampt:

var replaced = dao.SQLquery[0].replace(/  +/g, ' ');
for (var i = 0; i < replaced.length; i++) {
  console.log(replaced[i], sqlQuery0[i]);
  if (replaced[i] != sqlQuery0[i] ){
    console.log('|'+ replaced.slice(i-10,i+10)+'|',replaced.slice(i-10,i+10).length);
    console.log('|'+sqlQuery0.slice(i-10,i+10)+'|',sqlQuery0.slice(i-10,i+10).length);
    break
  }
}

gave me this:

n n
a a
m m
e e

L L
I I
K K
E E

' '
% %
% '
|me LIKE '%' AND inf| 20
|me LIKE '%' AND infl| 20

Apparently "%%" is interpreted as both % and %% how can this be ?

more:

var replaced = dao.SQLquery[0].replace(/  +/g, ' ');
console.log(sqlQuery0)
for (var i = 0; i < replaced.length; i++) {
  console.log(replaced[i], sqlQuery0[i]);
  if (replaced[i] != sqlQuery0[i] ){
    console.log('|'+ JSON.stringify(replaced.slice(i-10,i+10))+'|',replaced.slice(i-10,i+10).length);
    console.log('|'+ JSON.stringify(sqlQuery0.slice(i-10,i+10))+'|',sqlQuery0.slice(i-10,i+10).length);
    console.log('|'+ JSON.stringify(dao.SQLquery[0].slice(i-10,i+10))+'|',sqlQuery0.slice(i-10,i+10).length);
    break
  }
}

|"me LIKE '%' AND inf"| 20
|"me LIKE '%' AND infl"| 20
|"ame LIKE '%' AND in"| 20

In writing a test I was paring strings. And the test came back as Failing. I manually copy pasted the string and it works... Note the mysql string syntax; but it never touches mysql up to this point.

Console.logged copy+paste Both Strings look like this:

console.log(replaced);
"SELECT COUNT(*) FROM interaction WHERE ambassador_name LIKE '%' AND influencer_name = ?"

console.log(sqlQuery0);
"SELECT COUNT(*) FROM interaction WHERE ambassador_name LIKE '%' AND influencer_name = ?"

It Shouldn't be failing but it does. So I wanted to see where it fails:

it( "submits the proper first sql query", function(){
  var replaced = dao.SQLquery[0].replace(/  +/g, ' ');
  for (var i = 0; i < replaced.length; i++) {
    if (replaced[i] != sqlQuery0[i] ){
      console.log(replaced.slice(i-10,i+10));
      console.log(sqlQuery0.slice(i-10,i+10);
      break
    }
  }
})

TO My SUPRISE THE ABOVE RETURNED this..

me LIKE '%' AND inf
me LIKE '%' AND infl

They can't be different lengths.... right. I tried again.

  it( "submits the proper first sql query", function(){
    var replaced = dao.SQLquery[0].replace(/  +/g, ' ');
    for (var i = 0; i < replaced.length; i++) {
      if (replaced[i] != sqlQuery0[i] ){
        console.log('|'+ replaced.slice(i-10,i+10)+'|',replaced.slice(i-10,i+10).length);
        console.log('|'+sqlQuery0.slice(i-10,i+10)+'|',sqlQuery0.slice(i-10,i+10).length);
        break
      }
    }
  })

And to my bigger suprise:

|me LIKE '%' AND inf| 20
|me LIKE '%' AND infl| 20

But the First string is only length 19!

My last attampt:

var replaced = dao.SQLquery[0].replace(/  +/g, ' ');
for (var i = 0; i < replaced.length; i++) {
  console.log(replaced[i], sqlQuery0[i]);
  if (replaced[i] != sqlQuery0[i] ){
    console.log('|'+ replaced.slice(i-10,i+10)+'|',replaced.slice(i-10,i+10).length);
    console.log('|'+sqlQuery0.slice(i-10,i+10)+'|',sqlQuery0.slice(i-10,i+10).length);
    break
  }
}

gave me this:

n n
a a
m m
e e

L L
I I
K K
E E

' '
% %
% '
|me LIKE '%' AND inf| 20
|me LIKE '%' AND infl| 20

Apparently "%%" is interpreted as both % and %% how can this be ?

more:

var replaced = dao.SQLquery[0].replace(/  +/g, ' ');
console.log(sqlQuery0)
for (var i = 0; i < replaced.length; i++) {
  console.log(replaced[i], sqlQuery0[i]);
  if (replaced[i] != sqlQuery0[i] ){
    console.log('|'+ JSON.stringify(replaced.slice(i-10,i+10))+'|',replaced.slice(i-10,i+10).length);
    console.log('|'+ JSON.stringify(sqlQuery0.slice(i-10,i+10))+'|',sqlQuery0.slice(i-10,i+10).length);
    console.log('|'+ JSON.stringify(dao.SQLquery[0].slice(i-10,i+10))+'|',sqlQuery0.slice(i-10,i+10).length);
    break
  }
}

|"me LIKE '%' AND inf"| 20
|"me LIKE '%' AND infl"| 20
|"ame LIKE '%' AND in"| 20

• javascript

• 2 Can you please separately paste the result of each console.log() call? For all we know there are invisible unicode characters in one of the strings. – Matt Ball Commented Nov 17, 2015 at 20:24
• 2 What's sqlQuery0 in each of those? You're setting replaced to one thing but then paring it to some different string. In your second test, you say "But the First String is only length 19!" Which first string, and where do you get 19? You're printing the length of a 20 character substring (assuming i >= 10), so of course it's 20 characters long. I'm with @MattBall, there's probably a nul or something in one of the strings. Maybe write a function that dumps the position and character code for every character in a string, then call it with one string then the other and pare the output. – blm Commented Nov 17, 2015 at 20:31
• 1 Please show us what JSON.stringify(dao.SQLquery[0]) logs – Bergi Commented Nov 17, 2015 at 20:34
• sqlQuery[0] is the first output of a function the puts the string together sqlQuery0 is a prewritten string of what that output should look like – lonewarrior556 Commented Nov 17, 2015 at 20:34

3 Answers 3

expanding on what @Matt Ball and @blm said, you most likely have an 'invisible' character in the text that is in your 'replaced' variable. To test this, use a hex editor and paste the text from both the 'replaced' and sqlQuery0 variables in - you should be able to see that around the '%' character you have an invisible character like '00' NUL or '1E' record separator.

You can use this online Hex Viewer to test https://hexed.it/?hl=en

to see the ASCII codes I'm referring to, check out the ascii table http://www.asciitable./

As others have noted, you probably have some sort of hidden Unicode character in one of the strings. The best way to see is to use charCodeAt() to see what code points are actually there.

For instance:

var a = 'foo\u200bbar';
var b = 'foobar';

console.log('a = "' + a + '"');
console.log('b = "' + b + '"');

function toCodePointArray(str) {
    var result = [];
    for (var i = 0; i < str.length; i++) {
        result.push(str.charCodeAt(i));
    }
    return result;
}

console.log('toCodePointArray(a) = ', toCodePointArray(a));
console.log('toCodePointArray(b) = ', toCodePointArray(b)); 

When I run this, I get:

a = "foo​bar"
b = "foobar"
toCodePointArray(a) =  [ 102, 111, 111, 8203, 98, 97, 114 ]
toCodePointArray(b) =  [ 102, 111, 111, 98, 97, 114 ]

Here the hidden character shows up clearly as 8203, which is the decimal equivalent of 0x200b, which is the Unicode zero width space.

to avoid this problem that creates extra bits, use hex char val. for % it's 0x25

"SELECT COUNT(*) FROM interaction WHERE ambassador_name LIKE "X'250225'" AND influencer_name = ?"

or even

    "SELECT COUNT(*) FROM interaction WHERE ambassador_name LIKE "%X'250225'%" AND influencer_name = ?"

A second technique would consist in using Locate for simpler hex val binds but it s a bit slower.

"SELECT COUNT(*) FROM interaction WHERE LOCATE(X'25', ambassador_name) > 0  AND influencer_name = ?"

also, as Rob Wilson says, to avoid extra messy bits in unicodes when using LIKE, always bind PDO::PARAM_STR for LIKE. In the process of escaping injection, PDO also clean up those extra bits.

SELECT COUNT(*) FROM interaction WHERE ambassador_name LIKE ? AND influencer_name = ?

bindParam(1, "X'250225'", PDO::PARAM_STR);

or even

    bindParam(1, "%X'250225'%", PDO::PARAM_STR);