Updating to database using php

Question

The only way I got this to work was if I used the empty. However, this is not what I want. I want to be able to leave something empty if I have to. Does anyone know how I should change the code for this to work?

Edit page:

<form name="homePage" action="update.php" method="POST">
<Strong>Change home title:</Strong>
<p>
    <input style="width: 300px;" type="text" name="homeTitleChange" value="<?php echo $homeTitle ?>">
    <input type="hidden" name="rowHomeID" value="<?php echo $getHomeID?>">
</p>

<Strong>Change home subtitle:</Strong>
<p>
    <input style="width: 600px;" type="text" name="homeSubtitleChange" value="<?php echo $homeSubtitle ?>">
    <input type="hidden" name="rowHomeID" value="<?php echo $getHomeID?>">
</p>
<input type="submit" class="btn btn-skin" name="homepage" value="save" />
</form>

Query Page:-

include("../conn.php");
include("../conn.php");
if(isset($_POST['homepage'])){
    if(
        !empty($_POST["homeTitleChange"])&& 
        !empty($_POST["homeSubtitleChange"]) &&  
        !empty($_POST["rowHomeID"])
    ){
        $homeTitleUpdate = $_POST["homeTitleChange"];
        $homeSubtitleUpdate = $_POST["homeSubtitleChange"]; 
        $homeEditRow = $_POST["rowHomeID"];
        $query = "UPDATE Home SET 
            title = '$homeTitleUpdate', 
            subtitle ='$homeSubtitleUpdate' 
            WHERE homeID = '$homeEditRow' ";
        $result = mysqli_query($conn, $query) or die(mysqli_error($conn));

        if ($result) {
            echo "<p> - Success!</p>";
        }else{
            echo "<p> - Something went wrong</p>";
        }
    }
}

Thanks!

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Dec 13 '16 at 14:03
Firstly, prevent your [SQL injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). Secondly, debug your values & turn error reporting on and tell us the error. — Jaquarh, Dec 13 '16 at 14:04
I assume you mean that your database won't accept your query if some fields are empty. You can fix that on your database side by allowing nulls in your fields — WillardSolutions, Dec 13 '16 at 14:04
@EatPeanutButter `NULL` and `''` (empty string) are two different things — Daniel W., Dec 13 '16 at 14:37

score 1 · Answer 1 · edited May 23 '17 at 12:13

Precursors:

You have included your connection script twice.
You are including the hidden form field <input type="hidden" name="rowHomeID" value="<?php echo $getHomeID?>"> twice. This is inefficient.
Your form should have enctype='multipart/form-data' . Read Here

Without seeing your MySQL error we can't absolutely diagnose your problem, so instead I will give you the parts I know need to be fixed:

By default PHP string types will hold an empty string '' rather than a NULL value so I don't think your issue is empty values being inserted incorrectly (at least, not as described in your question).

$homeEditRow is the only required value. Because UPDATE table SET column=value WHERE column=<empty> will result in an error (or at the very least, no update).

Therefore replace:

if(
    !empty($_POST["homeTitleChange"])&& 
    !empty($_POST["homeSubtitleChange"]) &&  
    !empty($_POST["rowHomeID"])
 )

with:

if(!empty($_POST["rowHomeID"]){
     //run MySQL Update query.
}

Also, if the value is meant to be an integer, you can simply do this:

$homeEditRow = (int)$_POST['rowHomeID']; //force to int.
if($homeEditRow > 0 ){
  //run MySQL Update query.
}

Your other two values can be empty if you wish, that's fine.

BUT what these values can not contain is unescaped special characters in MySQL, typically (but by no means exclusively) ` , ', --, # characters.

So, it's best to clean unsafe characters from your user input.

Never Ever Trust User Input to be "safe"

$homeTitleUpdate = mysqli_real_escape_string($conn,$_POST["homeTitleChange"]);
$homeSubtitleUpdate = mysqli_real_escape_string($conn,$_POST["homeSubtitleChange"]); 
//assuming to be integer required
$homeEditRow = (int)$_POST["rowHomeID"];

This means any single quotes, or other unsafe characters do not interefere with your query execution. using Prepared statements is much safer than this method and is the recommended way of doing these things, you can use either PDO or MySQLi and there are many, many fine examples on Stack Overflow of these systems.

If you reach this point and you are still having issues, then you need to read what your MySQL error output is saying to you :

 //after your query regardless of outcome:
var_dump(mysqli_error($conn));

You may have issues such as you have a primary index column with two non-unique values (etc, etc). But we won't know for sure until you can output the MySQL error.

Finally, be careful with your IF statements checking if the Update Query was carried out because if nothing changed, there was no change to update, MySQL will not run the query, so could potentially return false when everything in fact ran correctly.

score 0 · Answer 2 · edited Dec 13 '16 at 15:58

Without specifying your errors, we can only assume your problem. Only you can debug your program, so for future notice please execute the following lines of code at the top of your scripts and tell us your errors.

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

Moving on, your script contains a condition that checks the values at the index in $_POST is !empty() but doesn't wrap around your Query. This meaning, whether or not the values are empty or set, your query will execute.

Assuming you only want to the query to run when there are values set, you can wrap this with an if expression:

 // an array of all the index's
$index = ['homeSubtitleChange', 'homeTitleChange', 'rowHomeID'];

 // loop through each index and check they're not empty
foreach($index as $_index)
{
    if( empty( $_POST[$_index] ) && !isset( $_POST['homepage'] ) )
    {
         // if empty - halt the program with an error
        die("Expected POST index: $_index or homepage.");
    }
}
unset($_index); //cleanup

 // if it got here - all index's have values
 // as Martin said in the comments, I assume you can wrap mysqli_real_escape_string()
 // and intval() ensuring the value is type (int) to protect
 // your database against SQL attacks
$subtitle = mysqli_real_escape_string($conn, $_POST[$index[0]]);
$title    = mysqli_real_escape_string($conn, $_POST[$index[1]]);
$row_id   = intval($_POST[$index[2]]);

 // consider upgrading to a PDO driver and using prepare() statements
 // this SQL statement is prone to SQL injections

$sql      = "UPDATE Home SET title = '$title', subtitle = '$subtitle' WHERE homeID = '$row_id'";

if( mysqli_query( $conn, $query ) )
{
    die("Success.");
}

die("Failed.");

I really want to +1 your answer but you do nothing even cursory about fixing the SQL injection vulnrabilities. You don't need PDO to use Parameterised queries... or you can even use `mysqli_real_escape_string` — Martin, Dec 13 '16 at 15:05
I updated the answer corresponding to the protection. I do not work with `MySQLi_*` so appologies; feel free to update the answer if you know better protection methods. @Martin — Jaquarh, Dec 13 '16 at 15:21
The only real improvement should be for OP to implement prepared statements as others have said in comments. — Martin, Dec 13 '16 at 15:38
I've never come across doing `unset()` after a `foreach()` loop. I like the principle of the *'**clean up**'*. @Martin — Jaquarh, Dec 13 '16 at 17:29
It's in the documentation, the variable value is left over once the loop completes. — Martin, Dec 13 '16 at 18:28
Well, I am 100% self taught so I miss these little things. It's why I use SO, to learn. @Martin — Jaquarh, Dec 13 '16 at 20:00

Brett · Answer 3 · 2016-12-14T07:47:47.070

As everyone else has said, please sanitize your user input; putting it directly into the database like that is very unsafe.

As for your question, from what I can understand you are trying to work out to make sure the values are set, but you also want to be able to pass an empty string!?

If so, I think you want isset.

//...
    if(
        isset($_POST["homeTitleChange"])&&
        isset($_POST["homeSubtitleChange"]) &&
        isset($_POST["rowHomeID"])
    ){
//...

This will make sure you POST values are set, which they should be anyway if they submitted the form; however it will also return true if the $_POST["rowHomeID"] = 0, which may not be what you want, so you may want to go back to using !empty for that which will mean it can't be an empty string or equal to 0.

score 0 · Answer 4 · answered Dec 13 '16 at 16:22

If I understand correctly, you want to allow empty string as input. If so, what you want is isset() instead of !empty().

So, this part in your code:

!empty($_POST["homeTitleChange"])&& 
!empty($_POST["homeSubtitleChange"]) &&  
!empty($_POST["rowHomeID"])

replace it with this:

isset($_POST["homeTitleChange"],$_POST["homeSubtitleChange"],$_POST["rowHomeID"])

and you're good to go.

Updating to database using php

4 Answers4

Never Ever Trust User Input to be "safe"