SQL Query 'WHERE EXISTS' if a column returns empty

Question

I am using this sql query as part of a PHP script to take user input (being applied to variables $condition1-$condition4) and compare it against a MySQL database and return relevant results.

My problem is that not all the forms on the site output a $condition4 value so it is not always inputted into the script/query.

I tried using the EXISTS predicate within the SQL query but could not get it to work.

Here is the query as i have it working:

 $sql = "SELECT columnXYZ
    FROM table_1
    WHERE condition1 = '" .$condition1."'
    and condition2 = '" .$condition2."'
    and condition3 = '" .$condition3."'
    and condition4 = '".$condition4."'";

Do I need to determine whether $condition4 was inputted before i run the query or is there a way to use the WHERE EXISTS predicate to achieve this?

The whole script: (var_dump to see the results of the query)

<?php
$condition1 = $_POST['condition1'];
$condition2 = $_POST['condition2'];
$condition3 = $_POST['condition3'];
$condition4 = $_POST['condition4'];
$dbhost = 'localhost';
$dbuser = 'admin';
$dbpass = 'pwd';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = "SELECT columnXYZ
        FROM table_1
        WHERE condition1 = '" .$condition1."'
        and condition2 = '" .$condition2."'
        and condition3 = '" .$condition3."'
        and condition4 = '".$condition4."'";

mysql_select_db('database_1');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    $columnXYZ = $row['columnXYZ'];
    var_dump($columnXYZ);   
} 



mysql_close($conn);
?>

The query works fine when $condition4 is inputted, as a work around for forms that do not have a $condition4 i have just been directing to a similar php script that has the $condition4 removed.

To clarify my question: Can i use the EXISTS predicate in a SQL query to determine if an input has a value or do i need to do so with PHP or some other method beforehand?

please see [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) and [MySQL Deprecation](http://php.net/manual/en/migration55.deprecated.php). Also [MySQLi](http://php.net/manual/en/book.mysqli.php) — Cayce K, Apr 28 '15 at 19:19
This code is open to SQL injection hacking. When you put data (ex: GET, POST, etc) into a query it must be sanitized. Please look up using PDO and parameterized queries to prevent people from hacking your code. — TravisO, Apr 28 '15 at 19:19
[You need to prevent SQL Injection!](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Apr 28 '15 at 19:20
thanks very much for the guidance on SQL injection prevention! — Vault Dweller, Apr 28 '15 at 20:57

Loopo · Accepted Answer · 2015-04-28T19:21:19.230

4

Just check if $condition4 is empty() before adding that part to your SQL query.

$sql = "SELECT columnXYZ
    FROM table_1
    WHERE condition1 = '" .$condition1."'
    and condition2 = '" .$condition2."'
    and condition3 = '" .$condition3."'";

if !(empty($condition4)){
    $sql .= "' and condition4 = '".$condition4."'";
}

As Seth mentions, google for 'SQL injection' if you're going to put this anywhere near the public internet.

edited Apr 28 '15 at 19:21

answered Apr 28 '15 at 19:15

Loopo

2,204
2
28
45

2

Should really mention sanitizing data here / sql injection – Seth T Apr 28 '15 at 19:18
Thank you, I did not realize I could 'interupt' the query like that with an 'if' statement. Thanks very much this clarifies things. – Vault Dweller Apr 28 '15 at 19:19
1

@VaultDweller just an FYI `.` in php is used for concatenation so that you can do what ever you want. Super useful tool :D – Cayce K Apr 28 '15 at 19:20

score 1 · Answer 2 · answered Apr 28 '15 at 19:19

1

When using empty() to check, the value of $condition4 might be null since empty allows for NULL values. I'm still learning PHP; however, would isset() be a better approach? Otherwise there might be condition4 = null

Also as that person commented on your post, please remember to validate all user input before you place it in a sql query or other places.

http://php.net/manual/en/function.isset.php

answered Apr 28 '15 at 19:19

Worldgorger

71
6

im googling sanitizing data / sql injection as Seth T suggested. any tips? – Vault Dweller Apr 28 '15 at 19:24
2

empty() will return true if the value is not set, null, empty string and such, which is what I assume we will want in this case. If there is no useful thing for the 'condition' to match, don't include that part of the query – Loopo Apr 28 '15 at 19:25
@VaultDweller I've toiled with mysql_real_escape_string and other sanitizing methods; however, as Jay Blanchard said, look into PDO( which is something I will do too ) – Worldgorger Apr 28 '15 at 19:28

SQL Query 'WHERE EXISTS' if a column returns empty

2 Answers2