保护sql注入的null列

问题描述：

hi i am having a contact form in my website where user can optionaly fill some of the fields and after click on submit button data save in to the database all of this worked fine until i decide to sanitize my code from sql injection as i mentioned at first before trying to sanitize it from sql injection it worked properly as i showed in below code

<form method="Post" action="">
        <input type="text" name="name" />name
        <select dir="rtl" style="width: 173px;" name="case" >
        <option value="" disabled selected hidden>اplease choose</option>
        <option  value='rent'>rent</option>
        <option  value='sell'>sell</option>
          </select >
          <input type="checkbox" name="check1" value='a'>apartment<br>
        <input type="submit" value="submit" />
        </form>
<?php
include("config.php");
    if(isset($_POST['submit'])){ 

        $date_clicked = date('Y-m-d H:i:s');

    }
    //insert to database
        $insert =mysqli_query($connect,"INSERT INTO $db_table VALUES (to simplify code i do not write this part)");

    }
    ?>

now i have to fill all the dropdown lists and checkboxes otherwise it gives error "column '' can not be null". also i can not insert date and time into database it gives the same error. here is my code when i protect it fron sql injection:

 <form method="Post" action="">
    <input type="text" name="name" />name
    <select dir="rtl" style="width: 173px;" name="case" >
    <option value="" disabled selected hidden>اplease choose</option>
    <option  value='rent'>rent</option>
    <option  value='sell'>sell</option>
      </select >
      <input type="checkbox" name="check1" value='a'>apartment<br>
    <input type="submit" value="submit" />
    </form>

    <?php
     include("config.php");
    if(isset($_POST['submit'])){ 

        $date_clicked = date('Y-m-d H:i:s');

    }
    if(isset($_POST['submit'])){ 
    //insert to database
    $query = mysqli_prepare($connect, "INSERT INTO $db_table VALUES (?,?,?,?)");
        /* bind parameters for markers */
        mysqli_stmt_bind_param( $query, "ssss", $_POST[name],$_POST['check1'],$_POST['case'],$_POST['date_clicked']);
    // execute query
    if ( mysqli_stmt_execute($query) ) {
      echo "Successfully inserted " . mysqli_affected_rows($connect) . " row";
    } else {
      echo "Error occurred: " . mysqli_error($connect);
    }

    }


    ?>

please help me

</div>

答

Make sure that your variables exist. This is necessary because your checkbox, for example, will be null if not checked and that could be a problem for the table you are using. You could set defaults and then insert it.

$name = !empty($_POST['name']) ? $_POST['name'] : '';
$check1 = !empty($_POST['check1']) ? $_POST['check1'] : '';
$case = !empty($_POST['case']) ? $_POST['case'] : '';
$date_clicked = date('Y-m-d H:i:s');

// prepare and bind
$stmt = $connect->prepare("INSERT INTO `$db_table` (`name`, `check1`, `case`, `date_clicked`) VALUES (?, ?, ?, ?)");
$stmt->bind_param("ssss", $name, $check1, $case, $date_clicked);

$stmt->execute();
$stmt->close();

保护sql注入的null列

相关推荐