There's a PHP script that pulls information from a MySQL database (posts from a forum), with one of the features being it can pull out and display posts based on a specified search string:
t.topic_title LIKE \'%host%\' OR t.topic_title LIKE \'%hosting%\'

This search string crashes the MySQL database. The author believes it's because there aren't any parentheses around the search query. I really want to use this script, but want to make sure I have the search string syntax right before I try so I don't kill the database.

Hi,

what do you mean with "crashes the MySQL database"?

The query simply doesn't work, or s.th. else?

Oh, btw. nevertheless, the query looks ok, but the beginning part is missing, maybe add it too in here.

Duncan

The query kills MySQL, and it takes a restart to get things working again.

Here's the config that's set by the user:
$CFG['search_string'] = 't.topic_title LIKE '%host%' OR t.topic_title LIKE '%hosting%'';
And here's the part of the php script having to deal with the search_string.
if ($CFG['search_string']) {
$sql .= ' ' . $CFG['search_string'] . ' AND';

Thanks for replying. Is this enough info?

There's something fishy about the actual syntax you've posted here...

I'm having a hard time following it. PHP has weird things it does with " & '... They do different things at different times. So that might be where the some of problem is...

But, an actualy mysql_query() is crashing the server?..

That really just doesn't sound right.

It might actually be a problem with mysql.... Try reinstalling it...

Are there another things that causes the server to crash?

What version of MySQL?

I don't know why it is crashing mySQL, but it looks like the syntax is incorrect. It is missing the quotation marks it should have. Since you are using the single quotation for the entire thing, you shouldn't use it for the variables inside. I think it should look like this:

$CFG['search_string'] = "t.topic_title LIKE '%host%' OR t.topic_title LIKE '%hosting%'";

kreativ,
to invistigate the problem I would put before the string containing "mysql_ query($sql);" that is following the piece of code you've provided with "echo $sql; exit;"
This will output the query and terminate the script. After that you could see the query and, if it looks OK, you may wish to try to run the query in phpmyadmin or console. Form this point, the possibilities are endless :)
And BTW, "like %hosting%" is a subset of "like %host%". But it shouldn't be a problem

Thanks for the replies! :) Sorry if I didn't make myself clear...I didn't think anyone would respond if I got too detailed about the entire script.

On MySQl 3.23.36, this search string killed MySQL:
t.topic_title LIKE '%web%' OR t.topic_title LIKE '%hosting%'

While adding parantheses around the whole search string made it run fine:
(t.topic_title LIKE '%web%' OR t.topic_title LIKE '%hosting%')

On MySQL 3.23.46, either one works. Go figure! I guess the newer version has some sort of protection scheme for idiots like me. :D

If you happen to know why the parantheses around the search string made a difference in the older version of MySQL, I'd love to hear it for learning's sake. :)

kreativ,
what you've typed here is not a "search string". It's a "where condition" of a mysql query.(I believe it's not even the entire condition, it's a part of it). The query begins with "select" and includes table names etc. One need to see the entire query to analize it properly. And I've got a strong feeling that your query may return unexpected results. As you said you'e got
"
if ($CFG['search_string']) {
$sql .= ' ' . $CFG['search_string'] . ' AND';
"
in your code. Looks like there's a second part of "where" condition after "AND".
It would mean your query had something like t.topic_title LIKE '%web%' OR t.topic_title LIKE '%hosting% AND something="$something"'. Which is not good sql. It should be t.topic_title LIKE '%web%' OR (t.topic_title LIKE '%hosting% AND something="$something")' or the variant you've proided.
Otherwise Mysql wouldn't know how to group "OR" and "AND" parts of the condition. Apparently the newer version is less sensetive about that.

#fdd700 - You're right. This must be the entire query:

//
// Create the sql statement.
// Note: In the WHERE clause there is a timelimit preventing
// corrupt postings with a date in the future from being
// fetched.
//

$sql = 'SELECT
f.forum_name,
p.enable_smilies,
p.post_id,
p.post_time,
pt.bbcode_uid,
pt.post_id,
pt.post_text,
t.forum_id,
t.topic_first_post_id,
t.topic_id,
t.topic_poster,
t.topic_replies,
t.topic_status,
t.topic_title,
t.topic_type,
t.topic_vote,
u.username,
u.user_email,
u.user_id
FROM
' . TOPICS_TABLE . ' AS t,
' . USERS_TABLE . ' AS u,
' . POSTS_TEXT_TABLE . ' AS pt,
' . POSTS_TABLE . ' AS p,
' . FORUMS_TABLE . ' AS f
WHERE
t.forum_id IN (' . $forums_id . ') AND
t.topic_poster = u.user_id AND
t.topic_first_post_id = pt.post_id AND
t.topic_first_post_id = p.post_id AND';
if ($CFG['date_offset_start']) {
$sql .= ' p.post_time >= ' . $CFG['date_offset_start'] . ' AND';
}
if ($CFG['date_offset_end']) {
$sql .= ' p.post_time <= ' . $CFG['date_offset_end'] . ' AND';
}
if (!$CFG['show_normal']) {
$sql .= ' t.topic_type != 0 AND';
}
if (!$CFG['show_sticky']) {
$sql .= ' t.topic_type != 1 AND';
}
if (!$CFG['show_announcement']) {
$sql .= ' t.topic_type != 2 AND';
}
if (!$CFG['show_locked']) {
$sql .= ' t.topic_status != 1 AND';
}
if (!$CFG['show_moved']) {
$sql .= ' t.topic_status != 2 AND';
}
if (!$CFG['show_poll']) {
$sql .= ' t.topic_vote != 1 AND';
}
if ($CFG['search_string']) {
$sql .= ' (' . $CFG['search_string'] . ') AND';
}
$sql .= ' t.forum_id = f.forum_id
ORDER BY
p.post_time DESC';
if ($CFG['number_of_posts'] != 0) {
$sql .= '
LIMIT
0,' . $CFG['number_of_posts'];
}

//
// Fetch topics/postings.
//

$posts = _fetch($sql);

//
// Return the result.
//

return $posts;

}


The author has changed:
if ($CFG['search_string']) {
$sql .= ' ' . $CFG['search_string'] . ' AND';
to what is above:
if ($CFG['search_string']) {
$sql .= ' (' . $CFG['search_string'] . ') AND';
This is apparently to ensure that nobody specifies a search_string without the ( ) around it.

And the user-definable config remains:
$CFG['search_string'] = 't.topic_title LIKE '%web%' OR t.topic_title LIKE '%hosting%'';

Without the ( ) around the search_string, it crashed MySQL 3.23.36 but did not crash 3.23.46. My newer MySQL version theory was crushed when it also crashed 3.23.47, so it must be some other config option.

The author still warns that using a bad search string could crash the server, so I'm hoping for some feedback on how to avoid this. And am also curious why the ( ) make such a big difference on some MySQL installations.