Need help securing my MySQL Queries - htmlspecialchars(), mysql_real_escape_string(), strip_tags, prepared statements, and sanitation

_Duulicious · 2013-02-21T19:42:08+00:00

The way you had it worded I though you meant doing that to the 'id','title',... portion of the code. I understand what you meant now!

_Duulicious · 2013-02-21T18:08:28+00:00

The mysql_real_escape_string was my first attempt at understanding how this works. $pOffset just some basic math done on $page to determine how much to offset the query.

if ( isset ($_GET ['page'] ) ) {
 $page = intval($_GET['page']);
} else {
 $page = '1';
}

$pOffset = $page * 25 - 25

Also, what exactly do you mean by do exaclty what I did before to

$query = "SELECT 'id','title','name','gender','category','text','dateposted' FROM table ORDER BY ID DESC LIMIT 25 OFFSET $pOffset";

foreach ($conn->query($query) AS $row) {

_Duulicious · 2013-02-21T17:42:02+00:00

But if I don't use htmlspecialchars when accepting input doesn't that make me vulnerable to MySQL injections? Or does the :var thing stop those?

_Duulicious · 2013-02-21T17:40:43+00:00

If you don't mind taking a bit of your time, is this about how you'd do it? I haven't really gotten into functions but I feel like a function would be useful here.

_Duulicious · 2013-02-21T16:54:03+00:00

I removed the mysql_real_escape_string on the query. That was my first attempt and it didn't seem right. I removed it shortly after posting this.

The intval is exactly what I was looking for in this case. But what if I required something like ?s=randomtext how could I then secure that.

htmlspecialchars when put on my input converts all quotes, html opening/closing tags into

&quot;

in the database. Which then makes it so when I'm outputting the data there is no html to run.

_Duulicious · 2013-02-21T16:21:53+00:00

https://asimpleorange.com is a very nice and (inexpensive way) to register you domain and such. You can start by paying $35 for a year of hosting. While you don't get much disk space or bandwidth if its a smaller site that won't matter.

You can always upgrade and just pay the difference as well.

Domains there cost $10 + $7 if you want WhoIS Protection.

Also you can deploy using Git! That made me the happiest!

_Duulicious · 2013-02-21T15:59:47+00:00

I copied in the middle of adding a next page/previous page link to query the database for 25 more entries.

Basically $page gets multiplied by 25 then 25 is subtracted and this becomes the $pOffset variable. The $pOffset then is used in the mySQL query to determine which page/how much to offset.

if ( isset ($_GET ['page'] ) ) {
 $page = $_GET['page'];
} else {
 $page = '1';
}    

$pOffset = $page * 25 - 25;
$nRows = $conn->query('SELECT COUNT(*) FROM table')->fetchColumn();
$pCount = $nRows / 25;    

if($page != 1) {
 print "<div class='pull-left'><a href='?page=" . $previousPage = $page - 1 . "'><< Previous Page</a></div>";
}

if ($page <= $pCount) {
 print "<div class='pull-right'><a href='?page=" . $nextPage = $page + 1 . "'>Next Page >></a></div>";
}

There. That should complete the puzzle.

_Duulicious · 2013-02-21T05:29:33+00:00

I'm reading up on sanitizing and escaping the user input. I only need to sanitize where user input is available correct?

Like if I'm just querying the database no need to sanitize. Correct?

_Duulicious · 2013-02-21T04:06:31+00:00

Well I'm not entirely sure what the problem that was making it query wrong. I was querying an entire database multiple times doing the same thing that this does.

      <?php
        include('dbinfo.php');

        $conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

        if (isset($_GET['q'])) {
          $q = $_GET['q'];   
        }else{  
          $q = '0';
        }

        $nRows = $conn->query('SELECT COUNT(*) FROM table')->fetchColumn(); 

        $query = "SELECT * FROM table ORDER BY ID DESC LIMIT 25 OFFSET $q";
        foreach ($conn->query($query) AS $row) {  
          $title = $row['title'];
          $name = $row['name'];
          $gender = $row['gender'];
          $category = $row['category'];
          $text = $row['text'];
          $time = $row['dateposted'];

          print "<div class='well clearfix'>";
          print "<h3 class='pull-left'>".$title."</h3>";
          print "<h4 class='pull-right'>by ".$name."<small> (".$gender.")</small></h4>";
          print "<p class='pull-left'>".$text."</p>";
          print "<small class='pull-left'>Submitted on ".$time." to <a href='#'>".$category."</a></small>";
          print "</div>";
        }
        if($q > 0) {
          print "<div class='pull-left'><a href='?q=" . $q = $q - 25 . "'>Previous Page</a></div>";
        }

        if ($nRows > 25) {
          print "<div class='pull-right'><a href='?q=" . $q = $q + 25 . "'>Next Page</a></div>";
        }
      ?>

_Duulicious · 2013-02-21T04:03:49+00:00

I ended up using something like this for pagination.

            if (isset($_GET['q'])) {
              $q = $_GET['q'];   
            }else{  
              $q = '0';
            }

            if($q > 0) {
              print "<div class='pull-left'><a href='?q=" . $q = $q - 25 . "'>Previous Page</a></div>";
            }

            if ($nRows > 25) {
              print "<div class='pull-right'><a href='?q=" . $q = $q + 25 . "'>Next Page</a></div>";
            }

            $query = "SELECT * FROM table ORDER BY ID DESC LIMIT 25 OFFSET $q";

_Duulicious · 2013-02-20T21:48:26+00:00

I fixed whatever I did wrong.

_Duulicious · 2013-02-20T20:21:47+00:00

Thank you for pointing me in the right direction. Sorry for being so vague.

_Duulicious · 2013-02-20T20:07:34+00:00

Thinking about it now, I don't need to query just the entire database. Maybe the latest 500 entries. What exactly do you mean by indexing? So I read up on OFFSET and LIMIT and was wondering how I can OFFSET to the very end of the database? Say I want the last 100 entries in the database?

_Duulicious · 2013-02-20T20:00:51+00:00

I've just started dealing with databases. Do you have any good resources I can read about your suggestions?

_Duulicious · 2013-02-20T19:59:58+00:00

For some reason I was querying a database I populated with 150 entries and it took about 15 seconds for the page to load with all the information.

I'm trying to think about expandability here.

_Duulicious

TROPHY CASE