0

I find I need to write queries without using CI's active record frequently for example:

$query = "INSERT INTO votes (userid, itemId) VALUES ({$userId},{$itemId})
                      ON DUPLICATE KEY UPDATE weight = {$vote}";

$query = $this->db->query($query);

Should I be worried about performance or security loss because of this?

el_pup_le
  • 11,711
  • 26
  • 85
  • 142
  • You should be worried about portability `ON DUPLICATE KEY` is a MySQL specific feature, it won't work with any other RDBM. – RageZ Dec 24 '11 at 13:39
  • we cannot tell if its a security loss because you do not show how you secure your statement. performance wise it is likely somewhat faster because it doesnt need to go through the builder (CI's AR is not an AR but a query builder) – Gordon Dec 24 '11 at 13:43
  • @Gordon Updated question to show how I do query. – el_pup_le Dec 24 '11 at 14:10
  • thanks. if you do not sanitize the values you are interpolating into the query your code is wide open to sql injection. consider using Usman's solution below. – Gordon Dec 24 '11 at 14:28

2 Answers2

4

You may use Query Bindings, you can enjoy flexibility with security. But you my not be able switch database in future.

http://codeigniter.com/user_guide/database/queries.html

Example:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?"; 

$this->db->query($sql, array(3, 'live', 'Rick'));

The secondary benefit of using binds is that the values are automatically escaped, producing safer queries. You don't have to remember to manually escape data; the engine does it automatically for you.

Muhammad Usman
  • 12,439
  • 6
  • 36
  • 59
2

Performance shouldn't be an issue. If anything, it might be better.

You would be giving up on CodeIgniter's bult-in security features.

Check this out:

And here's a little more light reading:

Community
  • 1
  • 1
Ayman Safadi
  • 11,502
  • 1
  • 27
  • 41