3

I am very worried about sql injection. I have been reading up about it and been trying to prepare the following query:

$query_AcousticDB = "SELECT * FROM products WHERE Category = 'Acoustic ' ORDER BY RAND()";
$AcousticDB = mysqli_query($DB, $query_AcousticDB) or die(mysqli_connect_error());
$row_AcousticDB = mysqli_fetch_assoc($AcousticDB);
$totalRows_AcousticDB = mysqli_num_rows($AcousticDB);

which works great.

I thought that I only have to change to the following:

$query_AcousticDB = prepare("SELECT * FROM products WHERE Category = 'Acoustic ' ORDER BY RAND()");

However this doesn't work. I get the following error:Call to undefined function prepare()

I still would like to get my values as:<?php echo $row_AcousticDB['what ever']; ?>

Can somebody point me into the right direction?

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
Ria
  • 516
  • 6
  • 24
  • I think that you don't have fully understood how mysqli works. Try reading the manual thoroughly. – Joshua Feb 12 '15 at 15:09
  • The key to preventing SQL injection is that you not build your SQL statements out of variables containing untrusted data. You are not building your SQL statements with variables, so they are not vulnerable to SQL injection. – Andy Lester Feb 12 '15 at 15:40
  • possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Andy Lester Feb 12 '15 at 15:41
  • @AndyLester I think it shouldn't be classed as a duplicate, because the OP displays knowledge of SQL injection prevention techniques e.g. prepared statements but seems unsure of correct syntax – Matadeleo Feb 12 '15 at 15:49
  • It is also a very bad idea to use RAND in the MySQL as this will rewrite the entire table each time the RAND function is called, it is far more efficient to load the whole MySQL table into a PHP array and then to shuffle the array. – Martin Feb 12 '15 at 16:29

2 Answers2

1

How about this?

$category = "Acoustic";

$sql = "SELECT * FROM products WHERE Category = ? ORDER BY RAND()";
$stmt = $DB->prepare($sql);
$stmt->bind_param('s', $category);

$stmt->execute();
$row_AcousticDB = $stmt->get_result(); // altenative: $stmt->bind_result($row_AcousticDB);
$row_AcousticDB->fetch_array(MYSQLI_ASSOC)

If you let the user enter any data (in text boxes on website) or you pull anything out of database for use (risk of second order injection) make sure you sanitize it (cleanse it of any nasty tags like < or >) by using htmlspecialchars($category) or htmlentities($category).

With this method implemented into your code, you will be reasonably safe from SQL Injection :)

Matadeleo
  • 504
  • 8
  • 18
  • Thanks for your reply,I have tried this but I now the results don't display I can the following error: undefined variable $row_acousticDB. How can I display me results properly. Thanks for looking – Ria Feb 12 '15 at 14:36
  • With the execute() added it just gives me an blank page. Any idea's. Thanks again for your help – Ria Feb 12 '15 at 14:46
  • @Ria I've completed my answer, finally.. :) sorry about that – Matadeleo Feb 12 '15 at 15:27
  • Thanks for looking again. I'm sorry to say Im getting the following error: Call to undefined method mysqli_stmt::get_result(). – Ria Feb 12 '15 at 15:50
  • This can be caused by a few factors - Running a PHP version below 5.3.0, or possibly missing the SQLND DLL. Try uncommenting the line `extension=php_mysqli_mysqlnd.dll` in your PHP.ini file. If these don't fix your issue, you can use `bind_result` instead - see here: http://php.net/manual/en/mysqli-stmt.bind-result.php – Matadeleo Feb 12 '15 at 16:03
0

Try to make this variable global: Put this on the upper part of your script global $acousticDB; or else you may try this $acoustic='';

Cleb
  • 25,102
  • 20
  • 116
  • 151
Rosi-elyn Herrera
  • 11
  • 3