0

I am using a phpdao for my database operation. When I run this code there is a error

function queryByContentAndCreatedBy($city,$min,$max){
    $sql = "SELECT id FROM users WHERE city=? LIMIT ?,? ";
    $sqlQuery = new SqlQuery($sql);
    $sqlQuery->setString($city);
    $sqlQuery->setNumber($min);
    $sqlQuery->setNumber($max);
    return $this->executeUpdate($sqlQuery);
} 

public static function executeUpdate($sqlQuery){
    $transaction = Transaction::getCurrentTransaction();
    if(!$transaction){
        $connection = new Connection();
    }else{
        $connection = $transaction->getConnection();
    }       
    $query = $sqlQuery->getQuery();
    $result = $connection->executeQuery($query);
    if(!$result){
        throw new Exception(mysql_error());
    }
    return mysql_affected_rows();       
}

Connection.php

public function executeQuery($sql){
    return mysql_query($sql, $this->connection);
}

Then there is a error : 

You have an error in your SQL syntax ... near ('Pu\'m','d\'Artagnan','s\dsd') at line 1

Similar error is for this query

 $sql = "SELECT id FROM users WHERE city IN (?,?)";

In PDO this is a solution : see details

$dbh->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

Whats is a equivalent solution for this for my dao ?

Community
  • 1
  • 1
neel.1708
  • 315
  • 1
  • 4
  • 18

2 Answers2

0

you are doing it wrong. and solution from the linked question won't help you.

if your "phpdao" doesn't have a setArray() method, you have to create the query manually.

  1. Create a string that looks like (?,?,?,?,?,?) where number of ?s is equal to the number of members of your array.
  2. add this string into your query
  3. bind your array members in a loop.
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • Thanks Col,But all the set functions from this dao only sanitise the user input so i will have to do that as well but one more issue with this dao is it uses mysql_escape_string to sanitise user input which i believe deprecated,so should i also use PDO or can you suggest any other tool ? – neel.1708 Dec 01 '11 at 11:25
  • yes, that's the point. you have to sanitize the array members. in a loop. PDO is okay, I believe. Bot note that with PDO you will have exactly the same problem, whichever mode you choose – Your Common Sense Dec 01 '11 at 11:40
  • So $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false ); didn't this solve your problem ? and are there any other better tools for DB operation? – neel.1708 Dec 01 '11 at 11:55
  • it solved MY problem but your problem is different. Solution for your problem is posted in my answer - three items list. It fits both for the phpdao and PDO. – Your Common Sense Dec 01 '11 at 12:04
  • Actually this question is for all of you,Do you know any other php DB tool like PDO?,I know i can google it but if you know any other tool which you have used or you know which is worth using ,Please give me the link. – neel.1708 Dec 01 '11 at 12:48
  • I have my own lib but it is far from the state of giving it away. it has an array placeholder for the cases like this. But I know no other tools doing that. I think PDO is okay. – Your Common Sense Dec 01 '11 at 12:54
  • Thanks Col. Shrapnel,But I found the problem in my dao and fixed it ,There was problem in my setNumber() function where it was adding single quote to the value.I removed it and its working now but I do believe i need setArray() function ,will do that. – neel.1708 Dec 02 '11 at 05:30
  • @neel sounds strange to me. in your example you have not numbers but strings ('Pu\'m','d\'Artagnan','s\dsd'). Am I missed something? But anyway, if setNumber added quotes, it would spoil LIMIT clause - that's right – Your Common Sense Dec 02 '11 at 05:47
  • Yes,But I used $sqlQuery->setNumber($min); this method for setting $min in my example but you are right i didn't add this method in my post,But error was after LIMIT clasue. – neel.1708 Dec 02 '11 at 06:57
-1
  1. The syntax is LIMIT start, amount - you're using it as LIMIT start, end.
  2. You're using $sqlQuery->setStrig - that can't be right.
  3. Is $city an array? In that case you may like to use WHERE city IN ? instead.
Tom van der Woerdt
  • 29,532
  • 7
  • 72
  • 105
  • Tom van der Woerdt Thanks for your reply,$end contains the amount only and city is a string 'city name',By the way problem is whichever query i use where there is LIMIT or IN there is a error.I just want to know how to set ATTR_EMULATE_PREPARES this parameter manually for this dao. – neel.1708 Dec 01 '11 at 10:58
  • Then ask your questions more carefully :-) Anyway, to answer it: you're using `mysql_query()` which doesn't have prepared statements. Technically they already are emulated, so what you want is either already done or simply impossible. – Tom van der Woerdt Dec 01 '11 at 11:04