A Question about searching a MySQL database..

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Gonik
Forum Newbie
Posts: 19
Joined: Fri Aug 30, 2002 7:39 am
Location: Somewhere Around Nothing

A Question about searching a MySQL database..

Post by Gonik »

hello people.. thanx for taking time to read this :)

Yesterday i made a PHP Script so that i can store all my ebooks on the database, so i can easilly access them (and of course search them). The search engine is working fine for any books that their title DOESN'T CONTAIN any kind of quotes, single (') or double(").

This is the search function:

Code: Select all

<?php
function searchTheDatabase($what) {
	print("You searched for: <b><i>$what</i></b><br>\n");
	$query = "SELECT * FROM ebooks WHERE bookName LIKE '%".addslashes($what)."%' ORDER BY bookName ";
	$dbResult = mysql_query($query);
	if(!$dbResult) {
		print("<br />A Database Error Has Occured!<br />\n");
		print("Query Used: <b>$query</b><br />\n");
		print("MySQL Replied: <b>".mysql_error()."</b><br />\n");
	}
	else {
		print("<ul>\n");
		while($row = mysql_fetch_row($dbResult)) {    
			$bookID=$row[0];
			$bookName=$row[1];
			$bookSize=$row[2];
			$cdVol=$row[3];
			print(" <li>$bookID. <b>".stripslashes($bookName)."</b> (<font color="Brown"><b>$bookSize</b></font> KB) [CD Vol. <font color="#993366"><b>$cdVol</b></font>]</li>\n");
		}
		print("</ul>\n");
		if(!mysql_num_rows($dbResult)) {
			print("Query Used: $query<br />\n");
			print("Nothing found \n");
		}
	}
}
?>
The form that is used is:

Code: Select all

&lt;form method="post" action="search.php"&gt;
 Search The Book Title: &lt;input type="text" name="what" /&gt;&lt;br /&gt;
 &lt;input type="submit" name="searchTheDB" value="Search" /&gt; &lt;input type="reset" /&gt;
&lt;/form&gt;
When the form is parsed the following code is executed:

Code: Select all

<?php
if(isset($_POST['searchTheDB'])) {
	print("<hr />\n");
	print("<p class="head"><b>Search Results</b></p>\n");
	searchTheDatabase($_POST['what']);
}
?>
As i said to you the problem is when i try to search something that contains quotes e.g. When i type O'Reilly the following message is appeared:
You searched for: O''Reilly

Query Used: SELECT * FROM ebooks WHERE bookName LIKE '%O\\''Reilly%' ORDER BY bookName
Nothing found :-(
The booknames are stored in the database with the slashes added (have used the addslashes() function)

If I try the query WITHOUTe addslashes() function..

Code: Select all

<?php
$query = "SELECT * FROM ebooks WHERE bookName LIKE '%".$what."%' ORDER BY bookName ";
?>
once again the script fails and the following message appears:
You searched for: O''Reilly

Query Used: SELECT * FROM ebooks WHERE bookName LIKE '%O''Reilly%' ORDER BY bookName
Nothing found :-(
PS. Sorry for my bad english, and thanx a lot to anyone who may provide any help :)[/quote]
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Re: A Question about searching a MySQL database..

Post by delorian »

Gonik wrote:The booknames are stored in the database with the slashes added (have used the addslashes() function)
Have you seen it with your own eyes :D :?: As far I know the MySQL strips slashes before entering the info to database by itself, of course I can be wrong 8) . First of all, did you try to enter that query from console :?: Secondly if you enter the ' or " sign into your database use htmlspecialchars with ENT_QUOTES parameter whenever you insert the data into the database or you search through the base.
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

Yes, no need to use the addslashes() function.
Image Image
User avatar
Gonik
Forum Newbie
Posts: 19
Joined: Fri Aug 30, 2002 7:39 am
Location: Somewhere Around Nothing

Post by Gonik »

Hm if you say so..

I'm gonna try and remove the backslashes from the database.. I hope it works then :)

Thanx a lot both of you for your replies
Post Reply