Issue escaping single quote on mssql 2008 from php codeigniter

Question

I am doing an insert into a database where one of the columns that I am trying to escape are single quotes that a user might type. For example, it's, I am using the codeigniter framework and did the following to escape my column:

$test=$this->db->escape($test);

But I get an SQL-Server error when I submit. For example if I type it's, its trying to do this

INSERT INTO table (test) VALUES(''it''s'')

When it should be

INSERT INTO table (test) VALUES('it''s')

Why is it inserting extra single quotes?

Yes @LiamSorsby both are invalid , what it should do is this `'it\'s'` with the `\'` — ArtisticPhoenix, Jul 23 '15 at 15:57
when i run 'it''s' on the actual sql server it inserts fine when i run it with 'it\'s' its giving me an error — user3547086, Jul 23 '15 at 15:58
@ArtisiticPhoenix Yes I know that. I was just stating that the above answer says it should be the bottom one. Which is also invalid. — Liam Sorsby, Jul 23 '15 at 15:58
@LiamSorsby - seems we are both wrong. I was thinking MySql, seems there are other databases out there. — ArtisticPhoenix, Jul 23 '15 at 16:02
@ArtisiticPhoenix Yes, haha. Never used it and I new there were small differences however I wasn't aware that escaping like that was valid in MSSQL. It would be easier if there were just one standard. — Liam Sorsby, Jul 23 '15 at 16:05
Yeah, I am also used to writing on MySQL. There are definitely a lot of minor things that are different on MSSQL — user3547086, Jul 23 '15 at 16:08

Shawn Lehner · Accepted Answer · 2015-07-23T16:11:52.077

0

You are escaping your entire SQL statement when you should only be escaping the string you are inserting. For example, lets say you have the following code currently.

$test = "INSERT INTO table (test) VALUES ('" . $value . "');";

You should change it to be

$test = "INSERT INTO table (test) VALUES ('" . $this->db->escape($value) . "');";

EDIT

As Liam pointed out, since you are using Codeigniter, you may want to also consider using the query binding they provide. See this URL for more information: https://ellislab.com/codeigniter/user-guide/database/queries.html

edited Jul 23 '15 at 16:11

answered Jul 23 '15 at 15:58

Shawn Lehner

1,293
7
14

nice. I am going to try to do that – user3547086 Jul 23 '15 at 15:59
Great. Let me know if it works. I am far from a PHP expert so there might be an even more efficient way to do this :) – Shawn Lehner Jul 23 '15 at 16:03
it works, thanks :) it makes sense. I will accept your answer when I am able to – user3547086 Jul 23 '15 at 16:04
@ShawnLehner Not entirely sure however would this not be a better way. http://stackoverflow.com/questions/14156421/how-can-i-use-prepared-statements-in-codeigniter – Liam Sorsby Jul 23 '15 at 16:07
Query binding is always the preferred method in any language but not required as long as you are properly escaping your variables manually. – Shawn Lehner Jul 23 '15 at 16:10

Issue escaping single quote on mssql 2008 from php codeigniter

1 Answers1

EDIT