Sql injection get id solution

Question

I don't know how to make this code safe. I tried to use mysql_real_escape_string in the query variable like

$query = "select * from products where id= " . mysql_real_escape_string($products);

but didn't work, also tried to escape in the products variable, but got the same result. Any sugestions? Thanks.

<?php

/ Define vars.
$conn = mysql_connect('localhost', 'test', 'test');
$products = isset($_GET["products"]) ? $_GET["products"] : "";
$query = "select * from products where id=$products";

// List elements.
if ($conn)
{
 mysql_select_db('testsqli');
 $result = mysql_query($query);
 
 // Table head.
 echo '<table cellspacing="5" cellpadding="5">';
 echo '<tr>';
 echo '<td>Description</td>';
 echo '<td>Price</td>';
 echo '</tr>';
 
 // Empty table?
 if (@mysql_num_rows($result)==0)
 {
  echo '<tr>';
  echo '<td><i>That\'s all!</i></td>';
  echo '</tr>';
 }
  // Listing data in table.
  while ($row = @mysql_fetch_array($result))
  {
   echo '<td>'.$row['Description'].'</td>';;
   echo '<td>'.$row['Price'].'</td>';;
   
  }
 echo '</table>';
}
?>

either use mysqli or pdo - with prepared statements and bound parameters — Professor Abronsius, Mar 10 '15 at 22:34
^ that helps, but let's not leave XSS out of the picture. Prepared statements aren't a 100% solution. — Funk Forty Niner, Mar 10 '15 at 22:35
@RamRaider I agree with that, but I still want to do this in a old way — andrews, Mar 10 '15 at 22:35
Here http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection <= read that and https://www.owasp.org/index.php/XSS_%28Cross_Site_Scripting%29_Prevention_Cheat_Sheet — Funk Forty Niner, Mar 10 '15 at 22:35

ingyhere · Answer 1 · 2015-03-11T02:05:51.713

I'd use prepared statements instead of MySQL escaping. Escaping skips over some of the wildcards, such as '%' and '*' which could also provide unanticipated results.

$stmt = $dbh->prepare("SELECT * FROM PRODUCTS WHERE ID=?");
$stmt->bindParam(1, $products, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

// call the stored procedure
$stmt->execute();

Also, keep in mind the following measures keep it safe:

Never connect to the database as a superuser or as the database owner. Use always customized users with very limited privileges.
Check if the given input has the expected data type. (In this case, verify that $products is formatted as expected, i.e. if your product catalog has indices of only nine characters, make sure it's not 100 characters long.) PHP has a wide range of input validating functions, from the simplest ones found in Variable Functions and in Character Type Functions (e.g. is_numeric(), ctype_digit() respectively) and onwards to the Perl compatible Regular Expressions support.
If the application waits for numerical input, consider verifying data with ctype_digit(), or silently change its type using settype(), or use its numeric representation by sprintf().

Reference: http://php.net/manual/en/security.database.sql-injection.php

score 0 · Accepted Answer · answered Mar 10 '15 at 22:41

0

You still need to add quotes, (and use msqli instead) like:

$query = "select * from products where id='" . mysqli_real_escape_string($products)."'";

// or

$query = sprintf(
  "select * from products where id='%s'",
  mysqli_real_escape_string($products)
);

answered Mar 10 '15 at 22:41

Blablaenzo

1,027
1
11
14

yes, it was so easy. thanks. – andrews Mar 10 '15 at 22:49

Sql injection get id solution

2 Answers2