CODEWITHSUNDEEP
phpmysqlprepared-statement
Sauron
Sauron

Reputation: 6657

Using Prepared Statements within selected parameters

I have the prepared statements set to prevent SQL injection, defined as:

            if($stmt1 = $db->prepare("INSERT INTO `group_messages`(`group_message_sync_id`, `from_user_sync_id`, `to_user_sync_id`, `to_group_sync_id`, `sent_dt`, `read`, `read_dt`, `message_text`, `shared_campaign_id`, `shared_campaign_location_id`) 
                                    SELECT
                                        (SELECT 
                                         CASE WHEN
                                         max(group_message_sync_id) is null
                                         then 0
                                         else max(group_message_sync_id)
                                         end + 1
                                         from group_messages)
                                    , ?
                                    , b.user_sync_id
                                    , b.group_sync_id
                                    , NOW()
                                    , 0
                                    , 0
                                    , ?
                                    , ?
                                    , ?
                                    from users_groups b
                                    WHERE 
                                            b.status = 1
                                        and b.group_sync_id = ?
                                        and b.user_sync_id != '?;"))
            {
                $stmt1->bind_params("ssssss",$userId,$message,$campaign,$location,$toGroupId,$userId);
                $stmt1->execute();
                $stmt1->close();

As you can see, I am pass 6 parameters into this statement

However, the if-else clause surrounding this statement fails every time.

How can I get this to work?

Upvotes: 0

Views: 34

Answers (1)

Moppo
Moppo

Reputation: 19285

The error in you query is that you have a ' in b.user_sync_id != '?; You have to change that to: b.user_sync_id != ?;

Regarding the question from your comments about reporting the error, the best thing to do is using exceptions:

$db = new PDO('<params>');

//this will make PDO throws exceptions in case of errors 
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try 
{    
    $stmt1 = $db->prepare('<your query>');
    $stmt1->execute();    
}
catch (PDOException $e) 
{
    //here you handle the exception, i.e die and show the message
    die( PDO Exception: ' . $e->getMessage() );
}

This way, when a syntax error will occur, PDO will launch a PDOException that will be caught by your catch statement. Inside the statement you can, for example, print the exception message.

Upvotes: 1

Related Questions