-2

I have a script in PHP that performs a multyquery update after taking some values in different inputs form. Everything works fine if I fill all these forms and press save. However if I leave one field open I receive an error as that value can't be empty.

Now, what I'd like to do is that when the php/html form has an empty input field the record shouldn't be changed and keep the value is currently in the database.

Here's part of my current code

                                        $sql = "UPDATE task SET name='$aName', surname='$aSurname', htbTotal='$ahtbtotal' WHERE id=1;";
                                    $sql .= "UPDATE task SET name='$fName', surname='$fSurname', htbTotal='$fhtbtotal' WHERE id=2;";

                                    if ($conn->multi_query($sql) === TRUE) {
                                      echo "Record updated successfully";
                                      $risposta= "Record updated successfully";
                                    } else {
                                      echo "Error updating record: " . $conn->error;
                                       $risposta= "Error updating record: " . $conn->error;
                                    }

This is an input example

<input id="aName" name="aName" type="text" placeholder="">

Any suggestion?

Thanks in advance!

Update: Just to make more clear, I don't need a validation. I want the user to leave some input empty if they don't want to fill but this shouldn't rewrite the related row value inside the database

Porcac1x
  • 105
  • 9
  • https://stackoverflow.com/q/60174/2864740 - in addition to being not-insecure (as the shown code likely is), and avoiding simple accidental unexpected data, parameterized queries also simplify SQL. – user2864740 May 16 '20 at 22:22
  • Anyway, the code shown does _not_ contain anything that would cause "if I leave one field open I receive an error as that value can't be empty" - so where is _that_ coming from? – user2864740 May 16 '20 at 22:22
  • the word that you are looking for is "validation".. try googling this along with other details... you will find an answer... – Shoaeb May 16 '20 at 22:23
  • you need a validator for your form... catch the situation early and inform the user of his mistake before inserting into the database – Honk der Hase May 16 '20 at 22:23
  • I don't need a validation. I want the user to leave some input empty if they don't want to fill but this shouldn't rewrite the related row value inside the database – Porcac1x May 16 '20 at 22:33
  • make an if statement checking for the values. If value contains something, do the insert, else don't. But looking at your current code it should be the least of your worries. You're wide open to an SQLI attack. https://xkcd.com/327/ – Martin May 16 '20 at 22:33
  • Personally, I'd use a ternary operator and allow for empty values in your database by setting a default value. @Porcac1x – Funk Forty Niner May 16 '20 at 23:10
  • See about sql injection and the importance of prepared and bound queries – Strawberry May 16 '20 at 23:12

1 Answers1

-1
$sql = "UPDATE task SET";


if(strlen($_POST['aName'])>0)
{
$sql .= " name='$aName',";
}
if(strlen($_POST['aSurname'])>0)
{
$sql.=" surname='$aSurname',";
} 
if(strlen($_POST['htbTotal'])>0)
{
 $sql.=" htbTotal='$htbTotal'";
} 
 $sql.=" WHERE id=1;";

this way you can solve by comparison

Ahmet Kürşat AKICI
  • 70
  • 2
  • this is insecure, and vulnerable to sql injection see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – nbk May 16 '20 at 22:28
  • sometimes there are places that are not mandatory to fill in the form but should not be empty in the database when they are not. after all, this friend is looking for a solution proposal. I think make your suggestions so that the perspective of our friend changes. and i know sql injections. – Ahmet Kürşat AKICI May 16 '20 at 22:29
  • ok, when you know about sql injection, than do it the right way in your answer – nbk May 16 '20 at 22:33
  • I'll try this method Ahmet, thanks! I don't care about the injection at the moment as the form input field will be soon converted to allow numbers only – Porcac1x May 16 '20 at 22:36
  • An sql injection does not care about the data type that's supposed to go to your table. It's about breaking the code to execute a query the attacker performs themselves. – Martin May 16 '20 at 22:39
  • Porcac1x, If you are going to convert it to a number, you can write a small function and add it if it is just a number. If you want help with this, you can write a comment again. – Ahmet Kürşat AKICI May 16 '20 at 22:45
  • Thanks for the support Ahmet! This is also an internal application that user will be able to use after accessing to the VPN, security is not an issue here. It's so odd that someone needs to justify the reason of their question here :). Anyway, I tried your code but I keep getting an error if I don't fill all the inputs: Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id=1' at line 1 – Porcac1x May 16 '20 at 23:04
  • Oh, it's due to the comma, depending which field I leave empty it breaks the SQL code – Porcac1x May 16 '20 at 23:08
  • I have decided to go ahead and create different rules for each case based on the code that Ahmet has provided. if(strlen($_POST['name'])>0) { $sql .= "UPDATE task SET name='$aName' WHERE id=2;" ; } It's not the best way to do that but I need to update only 6 records, so six calls to the db won't be an issue. Thank you all, especially Ahmet – Porcac1x May 16 '20 at 23:35