0

Please help me to figure out this. The first query would return a message saying that 

Couldn't execute query: Unknown column 'ssd23' in 'where clause.

ssd23 is the value the $_POST will get for the pnumber from a html form. However, it would work if there are only digits.

$result = mysqli_query($con, "DELETE FROM Tools WHERE PartNumber = {'$_POST['pnumber']'}") or die ("Couldn't execute query: " .mysqli_error($con));

This second query below would work with both digits and characters after using a variable.

$test = $_POST['pnumber'];                  
$result = mysqli_query($con, "DELETE FROM Tools WHERE PartNumber = '$test'") or die ("Couldn't execute query: " .mysqli_error($con));
Perry
  • 11,172
  • 2
  • 27
  • 37
Anh
  • 3
  • 1
  • 4
    There is a SQL injection vulnerability in this code, which will mean that your site will be taken down by crackers in short order. Please don't go live with this code! – halfer Aug 03 '13 at 17:26
  • Use prepared statements to pass dynamic input into SQL queries. They are designed specifically to increase security, *and* they will help you avoid this specific problem in the future. – DCoder Aug 03 '13 at 17:27
  • There is obviously a missing quote around the *value* which is then treated as a *field name*. Amusingly enough, you have involuntary exploited at small extend the SQL injection vulnerability of your own code :D – Sylvain Leroux Aug 03 '13 at 17:27
  • Thanks for warning me on the SQL injection. I read a little about it & just got broad the idea. I am learning PHP and building a practice website with a database. Working the SQL interface which will be available after using logging in. :) Thanks Sylvain Leroux for pointing the missing quotes, that kinda answered my question :) – Anh Aug 07 '13 at 10:06

1 Answers1

1

replace this:

$result = mysqli_query($con, "DELETE FROM Tools WHERE PartNumber = {'$_POST['pnumber']'}") or die ("Couldn't execute query: " .mysqli_error($con));

With this:

$result = mysqli_query($con, "DELETE FROM Tools WHERE PartNumber = '" . $_POST['pnumber'] ."'") or die ("Couldn't execute query: " .mysqli_error($con));

Notice I did not take care of sql injection above

Even better is to use prepare statements that will secure your querys, in your case it will be something like this:

$sql= 'DELETE FROM Tools WHERE PartNumber= ?';      

$stmt = $con->prepare($sql); 
$stmt->bind_param('i', $_POST['pnumber']);
$stmt->execute();
Perry
  • 11,172
  • 2
  • 27
  • 37
  • 2
    Hi Perry. You've replicated the OP's security vulnerability in your example. If you see `$_POST` or other user input added into a query without escaping or parameterisation, be sure to warn the OP that their code is insecure. – halfer Aug 03 '13 at 17:27
  • 1
    @halfer, I add now a prepare statement example that is much better then the first option :) – Perry Aug 03 '13 at 17:30
  • Great stuff Perry! +1 – halfer Aug 03 '13 at 17:32
  • Thanks Perry! Google says prepared statement is the way to do it, rather than mysqli_real_escape_string or whatever I may have come up with :) Been working with online tutorials so far.I am still learning about Procedure & Object Oriented PHP, been mixing both together :( – Anh Aug 07 '13 at 10:29