mysql connect in PDO (xss and inject secure)

Question

At the moment I'm using;

<html>
<head>
</head>
<body>
<form name="frmSearch" method="get" action="<?=$_SERVER['SCRIPT_NAME'];?>">
  <table width="599" border="1">
    <tr>
      <th>Keyword
        <input name="txtKeyword" type="text" id="txtKeyword" value="<?=$_GET["txtKeyword"];?>">
        <input type="submit" value="Search"></th>
    </tr>
  </table>
</form>
<?
if($_GET["txtKeyword"] != "")
    {


    $objConnect = mysql_connect("XXXXX","XXXX","XXXX") or die(mysql_error());
    $objDB = mysql_select_db("XXXX");
    // Search By Name or Email
    $strSQL = "SELECT * FROM blogs WHERE (title LIKE '%".$_GET["txtKeyword"]."%' or metadescription LIKE '%".$_GET["txtKeyword"]."%')";
    $objQuery = mysql_query($strSQL) or die ("Error Query [".$strSQL."]");
    $Num_Rows = mysql_num_rows($objQuery);


    $Per_Page = 2;   // Per Page

    $Page = $_GET["Page"];
    if(!$_GET["Page"])
    {
        $Page=1;
    }

    $Prev_Page = $Page-1;
    $Next_Page = $Page+1;

    $Page_Start = (($Per_Page*$Page)-$Per_Page);
    if($Num_Rows<=$Per_Page)
    {
        $Num_Pages =1;
    }
    else if(($Num_Rows % $Per_Page)==0)
    {
        $Num_Pages =($Num_Rows/$Per_Page) ;
    }
    else
    {
        $Num_Pages =($Num_Rows/$Per_Page)+1;
        $Num_Pages = (int)$Num_Pages;
    }


    $strSQL .=" order  by id ASC LIMIT $Page_Start , $Per_Page";
    $objQuery  = mysql_query($strSQL);

    ?>
<table width="600" border="1">
  <tr>
    <th width="91"> <div align="center">CustomerID </div></th>
    <th width="98"> <div align="center">Name </div></th>
    <th width="198"> <div align="center">Email </div></th>
  </tr>
  <?
    while($objResult = mysql_fetch_array($objQuery))
    {
    ?>
  <tr>
    <td><div align="center">
        <?=$objResult["id"];?>
      </div></td>
    <td><?=$objResult["title"];?></td>
    <td><?=$objResult["metadescription"];?></td>
  </tr>
  <?
    }
    ?>
</table>
<br>
Total
<?= $Num_Rows;?>
Record :
<?=$Num_Pages;?>
Page :
<?
    if($Prev_Page)
    {
        echo " <a href='$_SERVER[SCRIPT_NAME]?Page=$Prev_Page&txtKeyword=$_GET[txtKeyword]'><< Back</a> ";
    }

    for($i=1; $i<=$Num_Pages; $i++){
        if($i != $Page)
        {
            echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i&txtKeyword=$_GET[txtKeyword]'>$i</a> ]";
        }
        else
        {
            echo "<b> $i </b>";
        }
    }
    if($Page!=$Num_Pages)
    {
        echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$Next_Page&txtKeyword=$_GET[txtKeyword]'>Next>></a> ";
    }

    mysql_close($objConnect);

    }   
?>
</body>
</html>

This works fine but I'd like to make it more secure so it can withstand injection and xss attacks etc. Basically I need to make it 100% secure.

I'm trying to change it to PDO and I have got the following so far;

$stmt = $pdo->prepare('SELECT * FROM blogs WHERE title LIKE = ?');
$stmt->execute($_POST['txtKeyword']);

Which I think is correct and as its two separate interactions with the DB it should be secure from injections (i think?)

problem is; 1) I'm not sure how to implement this and 2) what else is needed to make sure the search is secure

I'd really appreciate any help

Hi.This is a good pdo tutorial http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers.Also check http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection — Arun Unnikrishnan, Jul 14 '13 at 18:14
1. you need to add percent marks to your keyword. 2. "PDO connect" has nothing to do with " xss attacks etc." So, it's different question. — Your Common Sense, Jul 14 '13 at 18:15
Also use `htmlspecialchars($string, ENT_QUOTES, 'UTF-8')` when outputting data. — PeeHaa, Jul 14 '13 at 18:15
any chance you could give an example with the above details? — user1657967, Jul 14 '13 at 18:27

score -1 · Answer 1 · answered Jul 14 '13 at 18:16

-1

First the execute() construct expects only an array so, you have to make that

$stmt = $pdo->prepare('SELECT * FROM blogs WHERE title LIKE = ?');
$stmt->execute(array($_POST['txtKeyword']));

And second, if you want to prevent XXS, attacks, You should just use htmlspecialchars() function to sanitize you data, during outputting everything from your database, using htmlspecialchars() and it will convert all tags, so that you will be safe.

answered Jul 14 '13 at 18:16

Am all ears. Whats the DV about? – Jul 14 '13 at 18:17
Your first line is not completely true [Manual]{http://php.net/manual/en/pdostatement.execute.php}. Beware of PDO tag.PS not my DV. – david strachan Jul 14 '13 at 19:06
If fail to read where it says, it is not needed. – Jul 14 '13 at 19:07
`bindParam()` &`bindParam ()` No array required. PDO tag is prone to downvotes at the least excuse. Only in your answer was all it took.Be warned – david strachan Jul 14 '13 at 19:25
I know that in case of the two above, array is not required, but I fail to see the problem in this answer, anyway you are right – Jul 14 '13 at 19:27

mysql connect in PDO (xss and inject secure)

1 Answers1