CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Hailwood
Hailwood

Reputation: 92661

code igniter active records query missing apostrophes

Code igniter is spitting out:

A Database Error Occurred
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TEST, `cardNumber` = 411111........11, `cardExpiry` = 1212, `authCode` = 110200,' at line 1

UPDATE `pxpayRequest` SET `status` = 'approved', `cardName` = Visa, `cardHolder` = VISA TEST, `cardNumber` = 411111........11, `cardExpiry` = 1212, `authCode` = 110200, `dpsTxnRef` = 0000000800b51dde, `dpsMessage` = APPROVED WHERE `id` = 1

And, as is obvious it is due to the missing sets of '

Why is code igniter not putting these in properly?

Here is my code:

$id = $result->MerchantReference;
$cardName = $result->CardName;
$cardHolder = $result->CardHolderName;
$cardNumber = $result->CardNumber;
$cardExpiry = $result->DateExpiry;
$authCode = $result->AuthCode;
$dpsRef = $result->DpsTxnRef;
$dpsMessage = $result->ResponseText;

$this->db->set('status', 'approved')
         ->set('cardName', $cardName)
         ->set('cardHolder', $cardHolder)
         ->set('cardNumber', $cardNumber)
         ->set('cardExpiry', $cardExpiry)
         ->set('authCode', $authCode)
         ->set('dpsTxnRef', $dpsRef)
         ->set('dpsMessage', $dpsMessage)
         ->where('id', $id)
         ->update('pxpayRequest');

You can see the associated values by looking at the query above.

Here is the print_r of $result

SimpleXMLElement Object
(
    [@attributes] => Array
        (
            [valid] => 1
        )

    [Success] => 1
    [TxnType] => Purchase
    [CurrencyInput] => NZD
    [MerchantReference] => 1
    [TxnData1] => SimpleXMLElement Object
        (
        )

    [TxnData2] => SimpleXMLElement Object
        (
        )

    [TxnData3] => SimpleXMLElement Object
        (
        )

    [AuthCode] => 121132
    [CardName] => Visa
    [CardHolderName] => VISA CARD
    [CardNumber] => 411111........11
    [DateExpiry] => 1212
    [ClientInfo] => 125.236.220.238
    [TxnId] => 4ddd9aa1dd14c
    [EmailAddress] => SimpleXMLElement Object
        (
        )

    [DpsTxnRef] => 0000000800b5d3c9
    [BillingId] => SimpleXMLElement Object
        (
        )

    [DpsBillingId] => SimpleXMLElement Object
        (
        )

    [AmountSettlement] => 8.00
    [CurrencySettlement] => NZD
    [DateSettlement] => 20110526
    [TxnMac] => BD43E619
    [ResponseText] => APPROVED
    [CardNumber2] => SimpleXMLElement Object
        (
        )

    [IssuerCountryId] => 0
)

Upvotes: 0

Views: 1153

Answers (4)

cartalot
cartalot

Reputation: 3158

I just went through this - i think part of the issue is that some of the values returned by simplexml are considered objects. whatever the reason -- you have to cast the values as string before inserting. Thankfully its really simple, just add (string)

so from this

$cardName = $result->CardName;

to this

$cardName = (string)$result->CardName;

Upvotes: 1

Roopa
Roopa

Reputation: 159

I just read a simple solution for this...

I changed the value of var $_escape_char (system/database/drivers/mysql/mysql_driver.php, line 36..

It was

var $_escape_char = '`';

Changed to

var $_escape_char = ' ';

and now it works... But i am affraid if I made any security issues..

Thanks

Upvotes: 0

Femi
Femi

Reputation: 64700

Quite odd: on any recent CodeIgniter that should work correctly. The only thing I can think of that might be off is that the db library isn't correctly identifying your database, or those variables are somehow not being identified as strings (so no escaping is happening). What does echo is_string($cardName); say?

Upvotes: 0

rasmusnord
rasmusnord

Reputation: 56

Try with the third parameter set to TRUE.

$this->db->set('cardHolder', $cardHolder, TRUE);

Upvotes: 0

Related Questions