1

The table has company names which are not escaped.
My qry looks like 

$sql = "SELECT id FROM contact_supplier WHERE name = '$addy' LIMIT 1";

The problem comes in where the company name values in the table are sometimes things like "Acme Int'l S/L".
(FYI: values of the $addy match the DB)
Clearly, the values were not escaped when stored.

How do I find my matches?


[EDIT]
Ahah!
I think I'm we're on to something. The source of the $addy value is a file

$addresses = file('files/addresses.csv');

I then do a 

foreach ($addresses as $addy) {}

Well, when I escape the $addy string, it's escaping the new line chars and including "\r\n" to the end of the comparison string.
Unless someone suggests a more graceful way, I guess I'll prob strip those with a str_replace().

:)
[\EDIT]

Atik
  • 177
  • 1
  • 18
mOrloff
  • 2,547
  • 4
  • 29
  • 48
  • Did you try to query the way it is right now? – Alex G Apr 12 '12 at 21:29
  • Yes, tried, but no luck. – mOrloff Apr 12 '12 at 22:13
  • Since I'm not yet able to answer my own questions until the next day, I EDIT'd in some content. Thanks-a-bunch :) – mOrloff Apr 12 '12 at 22:23
  • Stripping the newlines maybe helps, but that alone still won't be enough. You still *need* to use `mysql_real_escape_string` or parameterized queries as suggested in many of the answers. You should *ALWAYS* escape your string data when including it in an SQL statement. – Mark Byers Apr 12 '12 at 22:28
  • Thanks, I should have clarified that I intended to escape THEN strip new lines chars ... tried it and it worked perfectly ... thanks to ALL for walking me through this – mOrloff Apr 12 '12 at 22:33
  • @mOrloff: Escaping should always be the last thing you do. If you escape and then afterwards further modify the string there's a risk that you could introduce an error or even an SQL injection vulnerability. Without seeing the full code, I can't be sure if there's a vulnerability or not, but it's better not to take that risk in the first place. – Mark Byers Apr 12 '12 at 22:46

6 Answers6

0

Use mysql_real_escape_string:

$addy = mysql_real_escape_string($addy);

Or try using parameterized queries (PDO).

Regarding this statement:

Clearly, the values were not escaped when stored.

This is incorrect logic. If the values weren't escaped in the original INSERT statement, the statement would have failed. Without escaping you'd get an error along the lines of syntax error near "l S/L' LIMIT 1". The fact that the data is correctly stored in the database proves that whoever inserted it managed to do it correctly (either by escaping or by using parameterized queries).

If you are doing things correctly then the data should not stored in the database in the escaped form.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • That was one of the first things I tried ;), but alas ... NO rows then match because the DB has NON-escaped values ... IE: it's trying to match my escaped string "Acme Int\'l" to the DB value of "Acme Int'l" .. other thoughts ????? – mOrloff Apr 12 '12 at 22:01
  • 1
    @mOrloff: Sorry, but you're clearly mistaken. If the string is escaped in your SQL query it *will* match "non-escaped" values. In fact that's the *entire point*! Obviously there is a different reason why your string is not matching. And you asked for another thought... but did you even *try* using parameterized queries, which was my other suggestion? Or did you completely ignore that part of my answer? – Mark Byers Apr 12 '12 at 22:06
0

Why do you think the data already stored in the table should be escaped?

You should escape data only right before it is written directly into a text-based language, e.g. as a part of an SQL query, or into an HTML page, or in a JavaScript code block.

Dmytro Shevchenko
  • 33,431
  • 6
  • 51
  • 67
  • The most glaring for me rt now to enforse string scaping B4 storage in the DB is this VERY SAME problem I'm encountering ... other than that ... just good form. Then, B4 using in a codeblock of any sort, U simply strip slashes. ....... unless I've had it bass ackwards all these years :) :) – mOrloff Apr 12 '12 at 22:07
  • 1
    There's no universal kind of escaping. SQL has one set of special characters (`'`, `"`, etc.), HTML has other set (`<`, `>`, `&`, etc.). So never pre-escape data when stored. Always escape it right before you use it. – Dmytro Shevchenko Apr 12 '12 at 22:11
  • That makes some sense ... I'll look into it more. Thanks. – mOrloff Apr 12 '12 at 22:25
0

When the query is executed, there's nothing espaced. MySQL transforms it and inserts, otherwise it wouldn't insert and gives error because of syntax or we escape them for security like sql injection.

So your query with escaped values will be working fine with the data in your database.

Ata S.
  • 890
  • 7
  • 12
  • If I understand correctly, then my initial hunch was right along those lines ... I escaped the strings ... and got zero matches :( – mOrloff Apr 12 '12 at 22:12
  • Can you supply the value of the string you escaped, and the rows in the table should be matched? Maybe you are escaping 2 or more times which will result in \\\' characters. Also let me give an example, you have in your database "Ata's Home", which looks not escaped. You query that with ... WHERE column='Ata\'s Home' and that should be matched. If it doesn't match, look for the problem elsewhere. – Ata S. Apr 12 '12 at 22:16
0

If the values were not escaped when stored then they would have caused SQL errors when you tried to enter them.

The problem is that the data is not being escaped when you make the query.

Quick hack: Use mysql_real_escape_string

Proper solution: Don't build SQL by mashing together strings. Use prepared statements and parameterized queries

Community
  • 1
  • 1
Quentin
  • 914,110
  • 126
  • 1,211
  • 1,335
  • Thanks for the link ... this is a quick and dirty task, so I was reverting to whatever methods came out of storage (my dusty ol' brain) first ... lol ... clearly I should rethink this approach :D – mOrloff Apr 12 '12 at 22:37
0

Another option would be to change your query to this...

$sql = "SELECT id FROM contact_supplier WHERE name = \"$addy\" LIMIT 1";
The Man
  • 1,462
  • 3
  • 23
  • 45
  • Tried ... MySQL only wants the value in single quotes (even tested directly in PMA) :( ... other thoughts ?? – mOrloff Apr 12 '12 at 22:04
0

The issue turned out to be new-line characters The source of the $addy value starts out like this

$addresses = file('files/addresses.csv');

I then goes through 

foreach ($addresses as $addy) {}

When I escape the $addy string, it's escaping the new line chars and inserting "\r\n" on the end of the comparison string.
As soon as I dropped those chars with string_replace() after escaping, everything went swimmingly

Thanks-a-BUNCH for the help

mOrloff
  • 2,547
  • 4
  • 29
  • 48