Concatenate search options with IS NOT NULL

Question

edit final :

 $sql = "SELECT * FROM volunteers_database 
        WHERE ". $_GET['option'] ." like '". $_GET['search'] ."' 
        AND shelter_name IS NOT NULL AND shelter_address IS NOT NULL ";

this worked, SQL commands such as AND , IS NOT NULL appear to be string and not blue as SQL keywords SELECT , FORM WHERE but they are still working i was trying to over concatenate them so they appear blue as other SQL keywords .

So I am making a form where users can search for animal shelters using their city or pincode but i only want to display records where those details are NOT NULL ( by defualt they will be null ) I tried the query shown below but I know the syntax isn't right I also want to display user contact number where the shelter details are present currently, this code is redirecting me to the last else statement 'no shelter available'

edit : var dump :

string(126) "SELECT * FROM volunteers_database WHERE city ' like 'dehradun' 'AND shelter_name IS NOT NULL AND shelter_address IS NOT NULL "

error :

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' like 'dehradun' 'AND shelter_name IS NOT NULL AND shelter_address IS NOT NULL' at line 1 in C:\xampp\htdocs\College Project\shelter-search.php:21 Stack trace: #0 C:\xampp\htdocs\College Project\shelter-search.php(21): mysqli->query('SELECT * FROM v...') #1 {main} thrown in C:\xampp\htdocs\College Project\shelter-search.php on line 21

<form class="search--form" action="shelter-search.php" method="GET">
            <div >
                <h1 class="heading--lable">Find Shelters in Your CITY or Around Your Pincode </h1>
                <lable class="lable">Search</lable>
                <input class="input" type="text" name="search" placeholder="CITY/PINCODE" required="">
                    <label class="lable"  for="">Search Using</label>
                <select type="" name="option">
                        <option  value="city">Search CITY</option>
                        <option   value="pincode">Search PINCODE</option>
                </select>
                <input class="search-submit" type="submit" value="Find Volunteers"  >
          </div>
        
      </form>

$sql = "SELECT * FROM volunteers_database WHERE ". $_GET['option'] ." ' like '". $_GET['search'] ."'  '". "AND shelter_name IS NOT NULL AND shelter_address IS NOT NULL ";

<?php include_once( 'search-register-header.php' ); ?>
    <div class="search_reasult">
    
    <?php
    if ( empty($_GET['search']) && isset($_GET['option']) ){
        echo '<table id="" class="display">';
        echo "<tbody>";
        echo '<tr>';
        echo '<div class="enter--city-pincode">Please Enter City/Pincode</div>';
        echo '</tr>';
        echo "</tbody>";
        echo '</table>';
    } else {
        if( $conn->connect_error ){
                die("connection failed: ". $conn-> connect_error);
        }
        
            $sql = "SELECT * FROM volunteers_database WHERE ". $_GET['option'] ." ' like '". $_GET['search'] ."'  '". "AND shelter_name IS NOT NULL AND shelter_address IS NOT NULL ";


        $result = $conn->query($sql);
        echo '<h1 class="heading--lable">   Please be Respectful towards Shelter Management! </h1>';
        echo '<table id="" class="display">';
        echo "<thead>";
        echo '<tr>';
            echo '<td>Shelter Name</td>';
            echo '<td>Contact Number</td>';
            echo '<td>Shelter Address</td>';
            
        echo '</tr>';
        echo "</thead>";
        echo "<tbody>";
        echo '<tr>';
        if(!empty($result) && $result->num_rows > 0){   
            while($row = $result->fetch_assoc() ) {
               echo '<tr>';
                echo '<td>'. $row["shelter_name"] .'</td>';
                echo '<td>'. $row["contact_number"] .'</td>';
                echo '<td>'. $row["shelter_address"] .'</td>';
                echo '</tr>';
                
            }
        } else { 
            echo '<div class="enter--city-pincode">No Shelters Available</div>';
        }
        echo '</tr>';
        echo "</tbody>";
        echo '</table>';
    }
    ?>
    </div>

<?php include_once( 'footer.php' ); ?>

**Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, May 25 '22 at 12:48
https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use it again. — ADyson, May 25 '22 at 12:48
Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187) You should always use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenating user provided values into the query. Never trust ANY user input! — Alive to die - Anant, May 25 '22 at 12:49
P.S. Re your specific issue, you must write it like this: `AND shelter_name IS NOT NULL AND shelter_address IS NOT NULL`. You can't apply the same expression to two columns by using a comma - that's not valid. Each part of the WHERE clause must be self-contained. — ADyson, May 25 '22 at 12:49
Add `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` before your `mysqli_connect()` (or `new mysqli()`) command, and this will ensure that errors with your SQL queries are reported correctly to PHP automatically - it ought to have revealed the specific syntax error message to you. — ADyson, May 25 '22 at 12:50
Hi Thank You Guys yes i know its not safe against injections its just a college project and for limited time i just want to get it working — vishwajeetpanwar, May 25 '22 at 13:01
@ADyson can you help me concatenate the statement properly with other half I think wrong concatenation is causing the error still ! — vishwajeetpanwar, May 25 '22 at 13:02
`i know its not safe against injections its just a college project`...that's not an excuse for not learning to do things properly. In fact it's the exact time to learn how to do things properly, so that you don't get into bad habits and have to relearn it later. Also as I pointed out above, using parameterised queries protects you from more than just injection attacks. And they're not difficult to use, so there's no reason at all not to do it properly. No sane definition of "working" includes "contains obvious and known security defects" btw :-) — ADyson, May 25 '22 at 13:09
`I think wrong concatenation is causing the error still`....what exact error are you getting now? If you changed what I showed you then that removes one error. Did you add the mysqli error reporting as I showed? That will improve the quality of errors you get in some situations. Also what other debugging have you done? Have you made PHP echo or log your final SQL query so you can see what it looks like and analyse it for flaws / run it in Mysql directly? There's plenty more investigation you could be doing here. — ADyson, May 25 '22 at 13:10
@ADyson yes sir I know that its actully a college project and my other teammmates are brain dead so i have to do it solo i am actully learning laravel and will make this same project in laravel with everything safe and secure right now i just want it to be presentable and done quick — vishwajeetpanwar, May 25 '22 at 13:12
P.S. You can't parameterise column names, so `WHERE ". $_GET['option'] ." '` should be protected by a whitelist, so you check what `$_GET['option']` contains, and only allow it into the query if it matches one of the values in a predefined array. That way you don't allow random input. — ADyson, May 25 '22 at 13:12
@ADyson error : Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' like '231001' 'AND shelter_name IS NOT NULL AND shelter_address IS NOT NULL' at line 1 in C:\xampp\htdocs\College Project\shelter-search.php:21 Stack trace: #0 C:\xampp\htdocs\College Project\shelter-search.php(21): mysqli->query('SELECT * FROM v...') #1 {main} thrown in C:\xampp\htdocs\College Project\shelter-search.php on line 21 — vishwajeetpanwar, May 25 '22 at 13:12
You can do it safe and secure without needing Laravel. You might actually get it done quick by writing the queries properly btw, since it's a lot less vulnerable to silly SQL syntax errors. But anyway, I've made my point. — ADyson, May 25 '22 at 13:13
Please [edit] the question to show your error message, thankyou. And accompany it with the output of `var_dump($sql);` so we can see the exact query in full. — ADyson, May 25 '22 at 13:14
`WHERE city ' like` ...we can easily see there's only one quote mark and also an extra space before the one which was there. But also....table names must be quoted in backticks (`\` `) or not at all. They can't be quoted with single quote marks. `WHERE \`city\` like` would be correct. — ADyson, May 25 '22 at 13:26
Reference: [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — ADyson, May 25 '22 at 13:26
P.S. There's absolutely no point in using `LIKE` unless you also use at least one wildcard with it. Otherwise wou may as well just use `=` instead. https://www.tutorialspoint.com/sql/sql-like-clause.htm — ADyson, May 25 '22 at 13:27
Also there's an extra stray single-quote mark at the end here: `'". $_GET['search'] ."' '"` ..and **that one** would have been entirely eliminated by correct use of prepared statements and parameters, as I pointed out earlier. Look at the simple parameterised examples in https://phpdelusions.net/mysqli#prepare - as you can see, you don't even have to worry about the quote marks at all, it's all abstracted away. The SQL is also much easier to read without all the PHP-related string concatentation syntax etc. Therefore, such code is far more maintainable and reliable. — ADyson, May 25 '22 at 13:29

Concatenate search options with IS NOT NULL

0 Answers0