Posted by Harold Ralston on 03/31/06 07:49

Shaun-
Thanks! You've gotten me onto the correct path now.
Harold

s wrote:

>
> Great! Make sure to check out the PHP manual
> <http://www.php.net/manual/en/> and the MySQL documentation
> <http://dev.mysql.com/doc/> for answers that'll come even faster than
> those from the hardcopy library :)
>

>
> You're missing a couple of things, and you already know what one of
> them is. For the following code, instead of replicating your entire
> SQL query, I'm only going to show the line you've had trouble with
> (the AND clause)... Just picture it in context of the full query.
>
> First off, when you're using strings in queries, you've got to put
> them inside quotes, like so:
>
> AND (pictures.markername LIKE '$surname')
>
> Secondly, the LIKE operator in SQL is mostly useless without at least
> one % character, which SQL interprets as a wildcard. So, suppose
> $surname is "Smith" and you wanted to find records with a markername
> column that started with "Smith," you'd use:
>
> AND (pictures.markername LIKE '$surname%')
>
> The following fields would match:
>
> Smith
> Smithton
> Smithhardt
>
> ...but not Goldsmith.
>
> Or maybe you wanted it the other way around:
>
> AND (pictures.markername LIKE '%$surname')
>
> This would match:
>
> Smith
> Goldsmith
> Hammersmith
>
> ..but not Smithton.
>
> You can even combine them,
>
> AND (pictures.markername LIKE '%$surname%')
>
> That would match all of the names mentioned so far, and any other name
> that started with, ended with, or contained "Smith."
>
> If you're not looking to do "fuzzy" matching like this, and you only
> want an exact match, use the equals operator instead of LIKE:
>
> AND (pictures.markername = '$surname')
>
> One other thing you should be mindful of. If there's even the remotest
> possibility that your variable could contain an apostrophe, quotation
> mark, or other meaningful SQL character, you should escape the
> variable first. For example, imagine the following query when $surname
> = "O'Connor" ...
>
> SELECT * FROM userData WHERE surname='$surname'
>
> Suddenly it expands to:
>
> SELECT * FROM userData WHERE surname='O'Connor'
>
> ...which will generate an error, because SQL sees you comparing
> surname to 'O' and has no idea what the remaining Connor' is all
> about. Worse, failure to escape criteria can lead to SQL injection,
> which is mostly a fancy term for "random people can manipulate the
> records in your database." So, it's best to escape out of habit:
>
> $surname = mysql_real_escape_string($surname);
>
> ..prior to using $surname in your SQL query.
>
> A parting tip, use the mysql_error() function instead of simply dying
> with a static string:
>
> $result = mysql_query($query) or die(mysql_error());
>
> This will give you more detailed information about why your query
> failed.
>
> hth
>
> Shaun
>
> --
> <s@guerril.la.ape> (to email, remove .ape)
> --
>

[Back to original message]