[RESOLVED] Valid, working, multi-word, query breaks under certain conditions?

endl

Why does this query only return most of the keywords but comes up blank using a couple extra words that are in the exact same place as the rest of the query? All the words used alone work and combinations except one.

Using "Joe Whoever's Service and design" breaks the query even though many other combinations work. It seems to be that the "and" breaks it...the "and design" returns no results.

using the query:
$sql = "select blurb,business,url,description,keywords from sites WHERE keywords LIKE '%$search%' OR description LIKE '%$search%' OR business LIKE '%$search%' or url LIKE '%$search%'";

Any clues?

Thank you.

Piranha

I think that it is the ' char that breaks it. Do you do anything with the $search-variable before inserting it to the query? Anyway you should read up on database injection.

leatherback

How do you treat the string to go into the query? The single quote will break the query if you do not escape the string properly

endl

Thanks, but why just there? The rest of the query is inserted exactly the same way. 99% of it works just fine.

bradgrafelman

endl wrote:
The rest of the query is inserted exactly the same way.

Then put a single quote anywhere you have user-supplied input and watch your SQL query break.

User-supplied data should never be placed directly into a SQL query. Instead, it should be sanitized by a function such as [man]mysql_real_escape_string/man.

endl

So injection attacks can be sent from the browser? I do have an input check for every bit of input which is very strict.

bradgrafelman

Who says someone trying to do an injection is using a browser to interact with your script? And if your "input check" relies on Javascript, then it's not very "strict" for those who don't have Javascript enabled.

Besides, sanitizing input has dual benefits; sure, it protects against injections, but it also prevents certain characters from breaking SQL queries ("accidental" injections, if you will).

endl

Thank you for your reply.

I would like to know more about these injection techniques to protect myself. Not sure I completely understand.

If not through the browser then how?

My input checks are pure server side as much as I know how to do no javascript.

bradgrafelman

endl wrote:
I would like to know more about these injection techniques to protect myself. Not sure I completely understand.

Well unless you're using MySQLi or PDO and utilizing the prepared queries, you need to sanitize all user-supplied data (that inclues GET, POST, COOKIE, and even some of the data from the $_SERVER array) before using it in a SQL query. The [man]mysql_real_escape_string/man is the general way of doing this. I like to keep my code nice and clean by using [man]sprintf/man to build the query string:

$query = sprintf("INSERT INTO table1 (col1, col2, col3, col4) VALUES ('&#37;s', '%s', %d, '%s')",
    mysql_real_escape_string($_POST['field1']),
    mysql_real_escape_string($_POST['field2']),
    $_POST['field3'],
    mysql_real_escape_string($_POST['field4'])
    );

If you don't use sprintf() and the '%d' identifier for numbers, you could always cast them to integers/floats to sanitize them:

$id = (int)$_POST['id'];
// if a malicious script/user had sent "5 OR 1==1", $id now contains only 5

Essentially, you just want to prevent quotes, line breaks, and other characters from (intentionally or accidentally) altering or breaking your SQL queries.

endl wrote:
If not through the browser then how?

Command line, other PHP scripts, a botnet, etc. etc. - browsers aren't the only way to interface with websites.

endl wrote:
My input checks are pure server side as much as I know how to do no javascript.

Well it depends on what you mean by "check". If you strip out all non-alphanumeric characters for strings or any non-numeric characters for numeric values, then I guess you could consider the data already sanitized. Even so, I've always agreed that I'm "better safe than sorry" and have always sanitized external data before using it in SQL queries.

endl

Thanks. That was a huge help!