PHP SQL Check if form input exists in other table, then continue

Question

I'm writing a form where I want the code to check if the inserted 'date' on which a participant wants to attend an event matches a 'date' in my events table. For the moment (even though 'date' as on the form input doesn't match any of the dates on the events table) users can register for any date without getting the validation error.

on the html-form "register.html" I have:

    <form action="register.php" method="post">
      <div class="form-group">
        <label for="date"> Date of attendence </label>
        <input type="date" class="form-control" id="date" name="date"/>
      </div>
      <input type="submit" class="btn btn-primary" />
    </form>

on "register.php" I have:

    <?php
        $date = $_POST['date'];

        $conn = new mysqli('localhost','root','','skay');
        if($conn->connect_error){
            echo "$conn->connect_error";
            die("Connection Failed : ". $conn->connect_error);
        } else {
    
            $date_check_query = "SELECT COUNT * FROM events WHERE date='$date'";
            $number_of_matches = mysqli_query($conn, $date_check_query);

            if ($number_of_matches = 0) {
                array_push($errors, "No planned events on this day");
            } else {
                $stmt = $conn->prepare("INSERT INTO registrations(date) values(?)");
                $stmt->bind_param("sssssi", $date); //there are other 5 other values aswell
                $execval = $stmt->execute();
                echo $execval;
                echo "Registration successfully...";
                $stmt->close();
                $conn->close();
            }
        }       
    ?>

Grtz Beau

Looks like you're using unescaped input in your first query. That's a little dangerous... Your prepared statement is a lot safer. What is your question? — Jelmer, Feb 19 '21 at 11:37
`$number_of_matches` is not what you think at that point, so `if ($number_of_matches = 0)` is incorrect. Either find the number of affected rows ( num_rows ) or assign an alias to the `count(*)` and fetch that from the recordset — Professor Abronsius, Feb 19 '21 at 11:45
That prepared statement seems faulty. You need the same number of placeholders and parameters. — El_Vanja, Feb 19 '21 at 11:48

Goranov · Answer 1 · 2021-02-19T12:08:19.553

0

Double check your code, especially here

if ($number_of_matches = 0) {

Should be

if ($number_of_matches == 0) {

It's not into the scope of your questions but it's good ti think about SQL injection attacks and rework the following line of code:

$date_check_query = "SELECT COUNT * FROM events WHERE date='$date'";

edited Feb 19 '21 at 12:08

answered Feb 19 '21 at 11:36

Goranov

355
1
3
10

1

XSS concerns [web documents](https://stackoverflow.com/questions/15755323/what-is-cross-site-scripting), this here is [SQL injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). – El_Vanja Feb 19 '21 at 11:52
You are correct! – Goranov Feb 19 '21 at 12:07

score -1 · Accepted Answer · answered Feb 19 '21 at 11:57

If you assign an alias to the value returned by count(*) - like count(*) as 'aliasname', you can treat that as you would a regular column in the recordset.

Using a prepared statement to input data is fine but the good work is undone by using potentially malicious data directly in the select query - whereever you take user input you need to take extra precautions, so use prepared statements in all such cases.

<?php
    if( $_SERVER['REQUEST_METHOD']=='POST' && !empty( $_POST['date'] ) ){
    
        $date = $_POST['date'];

        $conn = new mysqli('localhost','root','','skay');

        $sql = "SELECT COUNT(*) as `total` FROM `events` WHERE `date`=?";
        $stmt=$conn->prepare($sql);
        $stmt->bind_param('s',$date);
        $stmt->execute();
        $stmt->bind_result($total);
        $stmt->fetch();
        $stmt->close();
        
        
        
        if( $total==0 ){
            $errors[]="No planned events on this day";
        }else{
            $sql='insert into `registrations`( `date` ) values(?)';
            $stmt=$conn->prepare($sql);
            $stmt->bind_param('s',$date);
            $stmt->execute();
            
            $stmt->close();
            $conn->close();             
            echo "Registration successfully...";
        }
    }
?>

PHP SQL Check if form input exists in other table, then continue

2 Answers2