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-29T14:37:12.290

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 '13 at 14:37

answered Jun 29 '13 at 14:15

Tim Fountain

33,093
5
41
69

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. – Slay Jun 29 '13 at 14:22
I've edited my answer to include an example - hopefully that's clearer. – Tim Fountain Jun 29 '13 at 14:37
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? – Slay Jun 29 '13 at 18:10
1

No, SQL injection can happen on any query that involves user input – Tim Fountain Jun 29 '13 at 18:11
hmm, so will sql injection happens in the above insert statement? how it protected? – Slay Jun 30 '13 at 19:14

Securing my zend_db_select insert statement

1 Answers1