Having trouble with a mysqli query - specifically the WHERE stoname= clause.
This doesn't work:
$result = @mysqli_query($dbc, "SELECT * FROM thedb WHERE coname='{$_SESSION['user']}' AND stoname='{$_SESSION['store']}' ");
If I echo $_SESSION['store'] then it prints as o\'store which matches what's the in the database. Yet this doesn't work.
However, if I echo mysqli_real_escape_string($dbc,($_SESSION['store'])) then it prints o\\\'store which is NOT what's in the database. Yet it works.
$result = @mysqli_query($dbc, "SELECT * FROM thedb WHERE user='{$_SESSION['user']}' AND stoname='".mysqli_real_escape_string($dbc,($_SESSION['store']))."' ");
I accept that I have working code, but I'm confused as to why this the case. Can anyone explain what I've done / am doing wrong? Thanks
If your table literally contains o\'store then mysqli_real_escape_string() has done its job correctly. The function's purpose is to escape a string for safe inclusion inside a SQL statement, not to be an exact literal match for what is actually already in your table.
The table value contains literally \'. When you use that value directly in the SQL statement, the backslash is misinterpreted as an escape character to the ' rather than as a literal \ as appears in your table. So the query produces no results because the executed SQL statement is:
# MySQL sees only an escaped ' and no \
SELECT * FROM thedb WHERE coname='something' AND stoname='o\'store'
...meaning the value of stoname actually compared is just o'store without \, because \ has been discarded by MySQL as an escape character.
So mysqli_real_escape_string() produces a value with two changes.
First, the literal ' in your original string is backslashed escaped as \' for use in the SQL.
Then, the literal \ already in your string is itself backslash-escaped so that it can be understood as a literal characer by MySQL rather than an escape character. That results in \\. Combined with the escaped \', you now have \\\'.
MySQL receives that string \\\' and is able to correctly interpret it as one literal \ followed by one literal ' after discarding the extra \ escape character before each. The condition matches the column's actual value and your query is successful.
# MySQL sees an escaped \ followed by an escaped '
SELECT * FROM thedb WHERE coname='something' AND stoname='o\\\'store'
We don't know much about how your table originally received its value, but I have a hunch it was stored in an escaped form. If the string o\'store was originally o'store without the \, it suggests that an escaped value was inserted in the table. That is not usually done, and is undesirable. Correct use of mysqli_real_escape_string() at the time of data insertion should store the original string rather than an escaped string. Escaping is only done when constructing SQL statements.