Help, using checkboxes to search against a DB

[redcow]

Hopefully someone can help me before I run to the top of a bell tower

 

Okay, so aside from the dramatics, total noob here, playing around with PHP/Jquery/mySQL.

 

I'm trying to search a database using three checkboxes, these are keywords that are sent via Jquery to a php file where i'll do a search against the database and throw back any articles that match the keyword.

 

The Jquery/Form part is working, i'm just a little lost (to say the least) on where to go in the PHP end of things. I'm following Learning PHP and MySQL by O'Reilly but I'm stuck here.

 

So the form looks like this:

 

<form id="findkeywords">
<ul>
<li><input type="checkbox" name="keywords[]"  value="keyword1" title="keyword1" /></li>
<li><input type="checkbox" name="keywords[]"  value="keyword2" title="keyword2" /></li>
<li><input type="checkbox" name="keywords[]"  value="keyword3" title="keyword3" /></li>
<li><input type="submit" name="submit" class="search" /></li>
</ul>
</form>

 

The jQuery that passes the checkboxes to the php file is as follows:

 

        <script type="text/javascript">
        $(document).ready(function(){
            $(".search").click(function()
            {           
                $.post("parser2.php", $("form#findkeywords").serialize(), 
                function(data)
                {        
                    $.each(data, function()
                    {   
                        $("div#result").append("<li class='arrow'><a href='parser2.php?id=" + this.id + "'>" + this.title + "</a></li>");
                    });
                    $("div#jsonContent").show();
                });
            });
        });
        </script>

 

 

Ok, so the trouble comes here, I want to take the checked boxes into the PHP file through $_POST, check for articles with the keywords and return them to the jQuery to be displayed, works fine with a simple 'type in and search', but I just can't get my head around the checkboxes.

 

The (horrid) code:

 

$link = mysql_connect("localhost","root","root");
mysql_select_db("keyworddb", $link);

foreach($_POST['keywords'] as $keyword => $value) 
{

$query = mysql_query("SELECT * FROM articles WHERE keyword LIKE '%". $value ."%' ");

$arr = array();

while( $row = mysql_fetch_array ( $query ) )
{
	$arr[] = array( "id" => $row["id"], "title" => $row["title"] );
}

echo json_encode($arr);
}

 

 

Any ideas? What am I doing so horribly wrong (apart from everything in parser2.php  )

 

Thanks!

[requinix]

Actually, for a "total noob", I think you're doing really well. Three issues:

 

The first one isn't about your problem. There's this evil little concept called SQL injection. Basically, unless you protect yourself, I can mess around with your SQL queries and completely bugger your site. It happens when you stick stuff from places like $_POST right into a query without validating or sanitizing them first.

The protection is really easy:

$query = mysql_query("SELECT * FROM articles WHERE keyword LIKE '%". mysql_real_escape_string($value) ."%' ");

 

The second one is kinda related. Normally, even with SQL injection, you'd be protected a little bit by MySQL itself. It wouldn't allow you to do all kinds of nasty stuff because you, the user who logged into it, aren't allowed to. However the root user can. root can do whatever it wants, and you are connecting using that account.

The fix is to create a user just for the website. A simple user that can do simple queries and nothing else. Doing that is easy too, but needs a bit to set up. Log into MySQL (like with phpMyAdmin or MySQL Workbench or even the MySQL console) as root and run

GRANT SELECT,INSERT,UPDATE,DELETE ON keyworddb.* TO 'username'@localhost IDENTIFIED BY 'password';

(Fill in "username" and "password" accordingly.) Then change your script's call to mysql_connect to match.

Note that this user can only run SELECT, INSERT, UPDATE, and DELETE queries. That means it can't, for instance, create tables. If you do need to do that, you connect using root and you make your changes. Because you are trustworthy; the people visiting your site are not.

 

 

Finally, the third issue. AJAX needs a single response - one object or one string or one whatever. And only one. But your script will output many: it returns an array for every keyword checkbox marked.

The simplest thing would be to 1) not clear out the $arr array inside the loop, and 2) only output it at the very end.

$arr = array();

foreach($_POST['keywords'] as $keyword => $value) 
{

$query = mysql_query("SELECT * FROM articles WHERE keyword LIKE '%". $value ."%' ");

while( $row = mysql_fetch_array ( $query ) )
{
	$arr[] = array( "id" => $row["id"], "title" => $row["title"] );
}

}

echo json_encode($arr);