Page 1 of 1

A Question about searching a MySQL database..

Posted: Sat Jun 21, 2003 1:12 pm
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]

Re: A Question about searching a MySQL database..

Posted: Sat Jun 21, 2003 3:42 pm
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.

Posted: Sat Jun 21, 2003 6:46 pm
by phice
Yes, no need to use the addslashes() function.

Posted: Sun Jun 22, 2003 4:03 am
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