SQL Injection Protection - Where and When

Question

I'm building a website which uses information passed through a URL to pick out information from a database table, but it was brought to my attention that doing this may cause a SQL Injection. As I thought this was only an issue where you were inserting information into a database, I'm a bit confused as to when, how and where you should protect your code.

Currently I have a url which looks like:

www.website.com/article.php?title=title&id=1

Which is shortened in htaccess to www.website.com/article/title/1

In my article.php page I then have:

<?php
if(isset($_GET["id"])){$url_id = $_GET["id"];}else{
header("Location: $site_url");
exit();
};
?>
<?php
if(isset($_GET["title"])){$url_title = $_GET["title"];}else{
header("Location: $site_url");
exit();
};
?>
<?php
$article_sql = "SELECT ...

I currently use mysqli_real_escape_string to prevent SQL Injection threats, but I'm unsure where to use it here. I'm guessing that adding...

...
<?php
if(isset($_GET["title"])){$url_title = $_GET["title"];}else{
header("Location: $site_url");
exit();
};
?>
<?php
$url_id = mysqli_real_escape_string($url_id); // ADDED
$url_title = mysqli_real_escape_string($url_title); // ADDED
?>
<?php
$article_sql = "SELECT 
...

Should do the trick, but is this correct?

Answer 1

You already use mysqli_ so take advantage of it. Here is simple example based on code you provide:

<?php
 // $mysqli is the connection
$article_sql = $mysqli->prepare("SELECT * FROM table WHERE url_id = ? AND url_title = ?");

$article_sql->bind_param("is", $url_id, $url_title);
$article_sql->execute();

$result = $article_sql->get_result();
while ($row = $result->fetch_array(MYSQLI_NUM)) {
  foreach ($row as $r) {
    print "$r ";
  }
  print "\n";
}