Asked  7 Months ago    Answers:  5   Viewed   47 times

I currently use Zend_Db to manage my queries. I've written already code that preforms queries like the one below:

$handle->select()->from('user_id')
                   ->where('first_name=?', $id)
                   ->where('last_name=?', $lname)

I've done this without sanitizing the input, assuming Zend_Db will. Does Zend do this?

Another question: Does Zend_Db sanitize insert('table', $data) and update queries?

Thanks.

 Answers

51

I wrote a lot of the code for database parameters and quoting in Zend Framework while I was the team lead for the project (up to version 1.0).

I tried to encourage best practices where possible, but I had to strike a balance with ease of use.

Note that you can always examine the string value of a Zend_Db_Select object, to see how it has decided to do quoting.

print $select; // invokes __toString() method

Also you can use the Zend_Db_Profiler to inspect the SQL that is run on your behalf by Zend_Db.

$db->getProfiler()->setEnabled(true);
$db->update( ... );
print $db->getProfiler()->getLastQueryProfile()->getQuery(); 
print_r $db->getProfiler()->getLastQueryProfile()->getQueryParams(); 
$db->getProfiler()->setEnabled(false);

Here are some answers to your specific questions:

  • Zend_Db_Select::where('last_name=?', $lname)

    Values are quoted appropriately. Although the "?" looks like a parameter placeholder, in this method the argument is actually quoted appropriately and interpolated. So it's not a true query parameter. In fact, the following two statements produce exactly the same query as the above usage:

    $select->where( $db->quoteInto('last_name=?', $lname) );
    $select->where( 'last_name=' . $db->quote($lname) );
    

    However, if you pass a parameter that is an object of type Zend_Db_Expr, then it's not quoted. You're responsible for SQL injection risks, because it's interpolated verbatim, to support expression values:

    $select->where('last_modified < ?', new Zend_Db_Expr('NOW()'))
    

    Any other part of that expression that needs to be quoted or delimited is your responsibility. E.g., if you interpolate any PHP variables into the expression, safety is your responsibility. If you have column names that are SQL keywords, you need to delimit them yourself with quoteIdentifier(). Example:

    $select->where($db->quoteIdentifier('order').'=?', $myVariable)
    
  • Zend_Db_Adapter_Abstract::insert( array('colname' => 'value') )

    Table name and column names are delimited, unless you turn off AUTO_QUOTE_IDENTIFIERS.

    Values are parameterized as true query parameters (not interpolated). Unless the value is a Zend_Db_Expr object, in which case it's interpolated verbatim, so you can insert expressions or NULL or whatever.

  • Zend_Db_Adapter_Abstract::update( array('colname' => 'value'), $where )

    Table name and column names are delimited, unless you turn off AUTO_QUOTE_IDENTIFIERS.

    Values are parameterized, unless they are Zend_Db_Expr objects, as in insert() method.

    The $where argument is not filtered at all, so you're responsible for any SQL injection risks in that one. You can make use of the quoteInto() method to help make quoting more convenient.

Wednesday, March 31, 2021
 
IvanH
answered 7 Months ago
55

You can run direct sql, using $db->query(); yours would simply be:

$results = $db->query("SELECT A.SOMETHING 
FROM A 
WHERE A.ID NOT IN (
    SELECT 
        B.TABLE_A_ID AS ID 
        FROM B 
        WHERE TABLE_C_ID = ?
)", $id);

EDIT: To answer whether this can be done with the object notation, yes:

$sub_select = $zdb->select()
                  ->from("b", array("table_a_id AS id"))
                  ->where("table_c_id = ?", 'a');
$select = $zdb->select()
              ->from("a", array("something"))
              ->where("id NOT IN ?", $sub_select);
print $select->__toString();

gives

SELECT `a`.`something` FROM `a`
WHERE (id NOT IN
    (SELECT `b`.`table_a_id` AS `id` FROM `b` WHERE (table_c_id = 'a')))
Wednesday, March 31, 2021
 
SilverHorn
answered 7 Months ago
52

You can use the TRIM function:

SELECT TRIM(title) as title, field2, field3 FROM products ORDER BY TRIM(title)

That ought to do it!

Wednesday, March 31, 2021
 
phpmeh
answered 7 Months ago
65

I've had trouble with this before, and found that the way to handle it is by not keeping sessions around. The trouble is you are trying to keep a connection open for way too long. Instead, use a thread local scoped session like so either in __init__.py or in a utility package that you import everywhere:

from sqlalchemy.orm import scoped_session, sessionmaker
Session = scoped_session( sessionmaker() )

Then set up your engines and metadata once. This allows you to skip configuration mechanics every time you connect/disconnect. After that, you can do your db work like this:

session = Session()
someObject = session.query( someMappedClass ).get( someId )
# use session like normal ...
session.close()

If you want to hold on to old objects and you don't want to leave your session open, then you can use the above pattern and reuse old objects like this:

session = Session()
someObject = session.merge( someObject )
# more db stuff
session.close()

The point is, you want to open your session, do your work, then close your session. This avoids timeouts very well. There are lots of options for .merge and .add that allow you to either include changes you've made to detached objects or to load new data from the db. The docs are very verbose, but once you know what you are looking for it might be a little easier to find.

To actually get all the way there and prevent the MySQL from "going away", you need to solve the issue of your connection pool keeping connections open too long and checking out an old connection for you.

To get a fresh connection, you can set the pool_recycle option in your create_engine call. Set this pool_recycle to the number of seconds of time in the connection pool between checkouts that you want a new connection to be created instead of an existing connection to be returned.

Tuesday, July 27, 2021
 
VieStar
answered 3 Months ago
44

you can use from_unixtime like

date_format(from_unixtime(t2.`time`), '%Y-%m')=date_format(now(), '%Y-%m')

But I think data type integer is not so suitable for this requirement

I would think using datetime will be more suitable, built an index on this column and this also make the filtering easier, like

t2.`time`>='2011-01-01' and t2.`time`<'2011-02-01'

or

date_format(t2.`time`, '%Y-%m')=date_format(now(), '%Y-%m')
Friday, August 13, 2021
 
devo
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :