mysql_real_escape_string with Zend

Question

I am developing a web application using zend framework. For select statements I have used following way.

Ex:

public function getData($name)
{
  $sql = "SELECT * from customer where Customer_Name = '$name'";
  return $this->objDB->getAdapter()->fetchAll ($sql);
}

This works fine. But If I send customer name as : colvin's place, The query fail. And I know it's because of the single quote.

Earlier I used addslashes PHP function. But I saw it is not a good way to do this. This time I used mysql_real_escape_string PHP function.

The issue is it says following warning.

Warning</b>: mysql_real_escape_string() [<a href='function.mysql-real-escape-string'>function.mysql-real-escape-string</a>]: Access denied for user 'ODBC'@'localhost' (using password: NO)

This is because of the mysql_real_escape_string function needs a connection to the database opened by mysql_connect. My question is how can I use this with *Zend_DB* classes. I need to use custom select queries always. Appreciate your other suggestions if available.

Thank you

Did yo try using [Zend_DB_Statement](http://framework.zend.com/manual/en/zend.db.statement.html) — Shiplu Mokaddim, Dec 31 '11 at 02:23
I use addslashes() and for integers Intval(). But using addslashes is a bad practice. — devOp, Jul 31 '12 at 14:03

score 8 · Accepted Answer · answered Dec 31 '11 at 03:19

8

You can use the quote() function provided by Zend_Db:

http://framework.zend.com/manual/en/zend.db.adapter.html#zend.db.adapter.quoting.quote

answered Dec 31 '11 at 03:19

leepowers

37,828
23
98
129

Thank you. I will check that. – Prasad Rajapaksha Dec 31 '11 at 03:32
3

The reason you don't just post link is because they tend to return 404 – Angelo Jan 18 '16 at 08:02

score 2 · Answer 2 · edited Oct 31 '14 at 15:50

2

You could use parameter binding as well, then the method will look like:

public function getData($name)
{
  $sql = "SELECT * from customer where Customer_Name = :name";
  return $this->objDB->getAdapter()->fetchAll ($sql, ['name' => $name]);
}

Then your data will be escaped automatically

edited Oct 31 '14 at 15:50

Kypros

2,997
5
21
27

answered Oct 31 '14 at 15:31

A. Martyn

85
1
9

score 2 · Answer 3 · answered Feb 24 '16 at 06:40

I had this problem, I used this way and is working correctly:

You can use quote():

The quote() method accepts a single argument, a scalar string value. It returns the value with special characters escaped in a manner appropriate for the RDBMS you are using, and surrounded by string value delimiters. The standard SQL string value delimiter is the single-quote (').

But quote returns a string with 'string' (return it inside quotation), for example I get an string from user from a input-text box (or by URL in GET method)

$string = $this->parameters['string']; // This is like $_POST or $_GET
$string = $this->db->quote($string);
$string = substr($string, 1, strlen($string)-2);   
//The above line will remove quotes from start and end of string, you can skip it

Now we can use this $string, and it is like what mysql_real_escape_string returns

why not simply trim($this->db->quote($string), '"\'"); – Jarek.D Sep 05 '17 at 14:26 — Jarek.D, Sep 05 '17 at 14:26

score 1 · Answer 4 · edited Jul 31 '12 at 15:16

1

I had the same problem and this solution works fine for me. I hope this will help. you can do something like this:

$quote_removed_name = str_replace("'","''",$name);

then write your query this way:

$sql = "SELECT * from customer where Customer_Name = '$quote_removed_name'";

edited Jul 31 '12 at 15:16

Tisho

8,320
6
44
52

answered Jul 31 '12 at 13:38

Uma

21
1

How do you ensure that there are no other characters that need escaping? `mysqli_real_escape_string()` knows them all, and they depend on the encoding setting of the database connection. – Sven Oct 11 '12 at 08:01
Furthermore, you don't always want to remove quotes (as yours is effectively doing). The solution you present isn't solving the problem, it's a band-aid for part of the problem. – TheMonarch Nov 14 '13 at 23:33

mysql_real_escape_string with Zend

4 Answers4

Linked