CODEWITHSUNDEEP
phpmysqlpdo
jflay
jflay

Reputation: 514

Having issues using PHP variables in PDO SQL statement

Ok, so I am needing either a workaround or some new direction with the way I am trying to run this query through PHP.

Basically, I only want to update columns if and only if the API string passes a string for that column. so If user updates name, but nothing else, then it only updates name column in the table. This explains the query... Now for the issue.

The issue is in order to compare if the API parameter being sent is blank or actually holds content (this is an iPhone App), I have to insert the PHP variable into the SQL statement and say, if this is blank then use the table's pre-existing value... this direct variable usage in the SQL is obviously causing errors. For example, if the password or any content with " or ' in the variable name, then it blows up the whole query because the variable is inserting an apostrophe into the SQL and screwing up the statement. No way to escape it that I know of and if I use PDO quote(), it inserts quotes in the database column and I don't want that! Is there a better way to do what I am trying to do? Please advise. I am open to completely redoing my statement and I don't like what I have because I think it is very labor intensive for the SQL server.

 //Assign passed parameters
$usr_id = $the_request['user_id'];
$usr_name = $the_request['name'];
$usr_email = $the_request['email'];//needs to re-validate
$usr_pass = $the_request['password'];//rehash
$usr_phone = $the_request['phone'];
$usr_img = $the_request['user_img'];

$queryUpdate = "UPDATE TABLE SET name = CASE WHEN '$usr_name' = '' THEN name ELSE ? END,
email = CASE WHEN '$usr_email' = '' THEN email ELSE ? END,
password = CASE WHEN '$usr_pass' = '' THEN password ELSE ? END,
phone = CASE WHEN '$usr_phone' = '' THEN phone ELSE ? END,
user_img = CASE WHEN '$usr_img' = '' THEN usr_img ELSE ? END
WHERE id = ? LIMIT 1";

if ($stmtUpdate =  $dbh->prepare($queryUpdate)) {
    $stmtUpdate->execute(array($usr_name,$usr_email,$hash,$usr_phone,$usr_img,$id));
        if( $stmtUpdate->rowCount() == 1 ) {
            echo json_encode(array('error' => ''));
        } else {
            echo json_encode(array('error' => 'Profile left unaffected'));
        }
}

Upvotes: 0

Views: 153

Answers (1)

Phil
Phil

Reputation: 164729

Why not build up the query as required, eg

if (!isset($the_request['user_id'])) {
    throw new RuntimeException('Required request param "user_id" not found');
}

// Filter request using array of allowed keys
$params = array_intersect_key($the_request, array_flip(['name', 'email', 'password', 'phone', 'user_img']));

// Remove any empty values
$params = array_filter($params);

if (!count($params)) {
    exit; // or throw an exception, just end the program here though
}

// Build an array of SET parameters
$set = array_map(function($key) {
    return sprintf('%s = :%s', $key, $key);
}, array_keys($params));

$params['id'] = $the_request['user_id'];

$sql = sprintf('UPDATE TABLE SET %s WHERE id = :id LIMIT 1', implode(', ', $set));
$stmt = $dbh->prepare($sql);
$stmt->execute($params);

Demo here - http://ideone.com/jQ9etj

Upvotes: 2

Related Questions