从php codeigniter发出关于mssql 2008的单引号的问题

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?

Answer 1

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