Securing my zend_db_select insert statement

Question

I am using zend_db_select class as my sql wrapper. I would like to know if the following insert statement is secured. (whether it provide mechanism to prevent sql injection etc)

function createNew($message,$tags,$userid,$imgsrc){
    $data = array('message' => $message,
                      'tags' => $tags,
                      'imgsrc' => $imgsrc,
                      'createdtimestamp'=>new Zend_Db_Expr('NOW()'),
                      'userid' => $userid);   
    $this->dbo->insert('data', $data);
    return $this->dbo->lastInsertId();
}

I tried insert a row into the table with some quotes, and it didnt get escaped with \ , am i worrying too much or does phpmyadmin auto removed the \ for easy viewing? Confused. I read somewhere that zend_db_select caters for sql injection stuffs like that.

Advice appreciated. thanks

Tim Fountain · Accepted Answer · 2013-06-29 14:37:12Z

2

Yes, this is secure. Zend DB uses prepared statements, so each of the values of your array are automatically escaped using the appropriate mechanism.

If you view the contents of your database (e.g. with a tool like phpMyAdmin), you should never see escaped quotes in there, this is the point of escaping data. If you insert the string O'Reilly, that's what ends up in your DB.

Edit: Consider this query:

INSERT INTO users (name) VALUES ('John O'Reilly')

this won't work and will give you an SQL syntax error, because the SQL parser will see the second ' as the end of the column value, and will then choke when it sees the character following. So you escape need to escape that quote:

INSERT INTO users (name) VALUES ('John O\'Reilly')

The backslash tells it to treat the apostrophe following as a literal (i.e. don't treat it as the end of the column value). The backslash itself is never inserted into the database. This is an escape character, which is a common practice in computing.

edited Jun 29, 2013 at 14:37

answered Jun 29, 2013 at 14:15

Tim Fountain

33.2k5 gold badges44 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Slay Over a year ago

hi tim! thanks for the reply! do not undersntand why we should never see the escaped quotes, why is it the point? the point about escaping data is to see our string get \' in the db. e.g we entered O'Reilly, how do we know the string is escaped if it appears as O'Reilly in the db.. i am confused about this point.

Tim Fountain Over a year ago

I've edited my answer to include an example - hopefully that's clearer.

Slay Over a year ago

oic! i just realized... so sql injections can only potentially happens for select, delete and update statements with where clauses? it won't happen for inserts?

Tim Fountain Over a year ago

No, SQL injection can happen on any query that involves user input

Slay Over a year ago

hmm, so will sql injection happens in the above insert statement? how it protected?

Collectives™ on Stack Overflow

Securing my zend_db_select insert statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related