variables need to wrapped as a string

Question

I have written this query,

    $sql = "SELECT `candidates`.`candidate_id`, `candidates`.`first_name`, `candidates`.`surname`, `candidates`.`DOB`, `candidates`.`gender`, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(`candidates`.`DOB`, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(`candidates`.`DOB`, '00-%m-%d')) AS `age`, `candidates`.`talent`, `candidates`.`location`, `candidates`.`availability`, `candidate_assets`.`url`, `candidate_assets`.`asset_size`
            FROM `candidates`
            LEFT JOIN `candidate_assets` ON `candidate_assets`.`candidates_candidate_id` = `candidates`.`candidate_id`
            WHERE `candidates`.`availability` = 'yes'";

            if(isset($type)) {
                $sql .= ' AND candidates.talent = '. "$type";
            }

            if(isset($skill))
            {
                $sql .= ' AND candidates.skill = '."$skill";
            }

            if(isset($gender))
            {
                $sql .= ' AND candidates.gender = '."$gender";
            }

    $query = $this->db->query($sql);

    return $query->result_array();

I wanting to the $type, $skill and $gender variable to passed as strings that whatever the variables contain are returned in the sql as wrapped in "" is this possible? How would I do this?

Are you using COdeIgniter or some other framework? the methods looks like so. Also based on your previous questions. If so, use $this->db->escape_string($value) — Damien Pirsy, Oct 26 '11 at 12:32
Why are people still using dynamically built queries + escaping nowadays? :/ — ThiefMaster, Oct 26 '11 at 12:33
@ThiefMaster Because we can't get rid of all the bad old PHP tutorials on the www. Same reason magic quotes always turns up in SO questions. — Michael Berkowski, Oct 26 '11 at 12:38
@Thief sure it isn't. Go try copy/paste in mysql console to debug. — Your Common Sense, Oct 26 '11 at 14:19
You also cannot copy&paste a string containing PHP variables. So if you replace placeholders or variables.. how does it matter? — ThiefMaster, Oct 26 '11 at 15:36

score 2 · Accepted Answer · edited Nov 03 '13 at 16:04

2

I'm basing on how the method looks and some of your previous question, and according to this you can use $this->db->escape_str($value) on those variables, so they're escaped as string no matter what type they are.

edited Nov 03 '13 at 16:04

Josh Crozier

233,099
56
391
304

answered Oct 26 '11 at 12:38

Damien Pirsy

25,319
8
70
77

score 0 · Answer 2 · answered Oct 26 '11 at 12:31

0

' AND candidates.talent = "'. $type . '"';

You might want to throw in a mysql_real_escape_string() to properly escape the input.

answered Oct 26 '11 at 12:31

CodeCaster

147,647
23
218
272

score 0 · Answer 3 · answered Oct 26 '11 at 12:32

0

Try this:

if(isset($type)) {
   $sql .= ' AND candidates.talent = "'. $type . '"';
}

Similarly for the other two clauses.

answered Oct 26 '11 at 12:32

Aziz Shaikh

16,245
11
62
79

score 0 · Answer 4 · answered Oct 26 '11 at 12:46

0

 if(isset($gender))
 {
      $gender = mysql_real_escape_string($gender);
      $sql .= " AND candidates.gender = '$gender'";
 }

answered Oct 26 '11 at 12:46

Your Common Sense

156,878
40
214
345

variables need to wrapped as a string

4 Answers4