How to concatenate equal sign and a variable such that result is usable in a mysql query of the type shown below?

Question

I am running a mysql query based on several variables being posted from a bootstrap 4 form. I am struck at how to concatenate one of the posted variable 'prof' so as to use it for the query in two different scenarios: (1) When the user makes no choice and a NULL value is posted (2) When the user selects a particular profession and a specific value is posted. I need to concatenate the variable in a manner that I get the result of the type: = 'P01' and not just = P01 as it won't work in the mysql query: I am posting part of the code to show how I am handling the posted variable and the query itself. The query also includes some of the variables that i have been able to use successfully.

if(isset($_POST['prof_match']) && ($_POST['prof_match']) != 'NULL') {
  $choice_prof = "= " . ($_POST['prof_match']); // Example P01 is Accountant
}else {
  $choice_prof = 'IN(SELECT prof FROM profiles)';
}

// The query is as follows:

SELECT * 
FROM profiles 
WHERE age $choice_age 
AND height $choice_ht 
AND edn $choice_edn 
AND prof $choice_prof;

The resulting string I get from the $choice_prof is quote = A01 unquote while what i need is quote = 'P01' unquote.

English not being my first language please ignore the syntax and grammatical mistakes. Thanks in anticipation.

i would suggest that you read what a [SQL injection](https://en.wikipedia.org/wiki/SQL_injection) is and [How you can prevent a SQL injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Raymond Nijland, Jun 17 '19 at 11:51
I would be using pdo binding to prevent sql injection but right now i am struck at how to concatenate the variable to be used in the query such that i don't have to put equal sign as the same query would be using the alternative response using IN(SELECT prof FROM profiles) syntax that doesn't use = sign. Thanks for your caution @ Raymond Nijland — Muhammad Khan, Jun 17 '19 at 12:06
Fair enough but most likely you want to rewrite `SELECT .. WHERE .. IN(...)` into using a join as `IN(SELECT ..)` tends to optimize bad if profiles is a "huge" table. — Raymond Nijland, Jun 17 '19 at 12:25
Maybe someone will hate me for the suggestion, but you could leverage the form `IN (?)`, instead of `= ?` to make the two queries more similar — salvatore, Jun 17 '19 at 12:29

score 2 · Answer 1 · answered Jun 17 '19 at 12:08

2

As a means to kill 2 birds (solving your problem and sanitising your inputs) with one stone (using a prepared statement). You can do:

$parameters = [ $age, $height, $edn ]; //Actual values here, not values with condition
$sql = 'SELECT * FROM profiles WHERE age = ? AND height = ? AND edn = ? AND prof';
if(isset($_POST['prof_match']) && ($_POST['prof_match']) != 'NULL') {     
  $parameters[] = $_POST['prof_match'];
  $sql .= '= ?';
}else {
  $sql .= ' IN(SELECT prof FROM profiles)';
}

You can then execute this as a prepared statement.

PDO example:

$statement = $pdoObject->prepare($sql);
$result = $statement->execute($parameters);

It is similar in MySQLi as well.

answered Jun 17 '19 at 12:08

apokryfos

38,771
9
70
114

The option where it uses `IN(SELECT prof FROM profiles)` is used seems excessive. – Nigel Ren Jun 17 '19 at 12:16
also to add to @NigelRen 's comment most likely you want to rewrite `SELECT .. WHERE ,, IN(...)` into using a join as `IN(SELECT ..)` tends to optimize bad if profiles is a "huge" table. – Raymond Nijland Jun 17 '19 at 12:24
@NigelRen I really didn't read that part that much. Just copy-pasted from the question. That part as it's written is actually not necessary but I'll keep it because (I'm assuming) OP wants it there (for some reason) – apokryfos Jun 17 '19 at 12:36

score 0 · Accepted Answer · answered Jun 17 '19 at 12:01

0

Its very straight forward to use php variable and generate an sql string.

If you need quotation marks around your variable yo put them in your sql string like so:

$sql = "select * from table where some_column = '$variable'";

In your case, just put them in your string like this:

if ( !empty($_POST['prof_match']) ) {
    $choice_prof = " = ' " . $_POST['prof_match'] . "'";
} else {
    $choice_prof = 'IN(SELECT prof FROM profiles)';
}

SELECT * 
    FROM profiles 
WHERE 
    age $choice_age AND 
    height $choice_ht AND 
    edn $choice_edn AND 
    prof $choice_prof;

For the empty() function refer to docs

answered Jun 17 '19 at 12:01

Abdul Rehman

1,662
3
22
36

5

Mind the SQL injections, as hackers/crackers/scriptkiddies would love to see this code used. – Raymond Nijland Jun 17 '19 at 12:04
^ Yes, As you have mentioned in your comment above as well. Thanks – Abdul Rehman Jun 17 '19 at 12:06
@Bsienn Thanks a lot. The second line of the code above is how i needed to concatenate my variables to be used in my query. And thanks to those who cautioned to use PDO prepared statement, I would exactly be doing that. Thanks indeed. – Muhammad Khan Jun 17 '19 at 12:26

How to concatenate equal sign and a variable such that result is usable in a mysql query of the type shown below?

2 Answers2