using special characters in mysql query throws exception

Question

this is mysqlquery

SELECT course_id
FROM course_master
WHERE year_id = '6'
AND course_name = ''What is Test?': Perspectives'

When i run the query it throws a syntax error because of special characters problem.Iam using mysql_escape_string to escape but it is not working.How to escape these characters

now I am interested in how you escape that string... could you provide that code? — Najzero, Feb 14 '13 at 13:10
please show the actual PHP code; we can't give you accurate help without it. — SDC, Feb 14 '13 at 13:18
Hi the actual code is $sql = "select course_id from course_master where year_id='".$year."' and course_name='".$this->sanitiseData($course_name)."'"; $course_id = $this->_dbAdapter->fetchOne($sql); public function sanitiseData($data) { $data = mysql_escape_string($data); return $data; } — srikanth s, Feb 14 '13 at 13:28

score 1 · Answer 1 · answered Feb 14 '13 at 13:14

1

I am using mysql_escape_string to escape but it is not working.

You are using it wrong. You have to format string values only, not whole query

// here are your variables
$year = 6;
$name = "'What is Test?': Perspectives";

// let's format them
$year = intval($year);
$name = mysql_real_escape_string($name);

// and then insert in a query
$sql  = "SELECT course_id FROM course_master 
         WHERE year_id = $year AND course_name = '$name'";

answered Feb 14 '13 at 13:14

Your Common Sense

156,878
40
214
345

Hi Iam Using in this way but not working $sql = "select course_id from course_master where year_id='".$year."' and course_name='".$this->sanitiseData($module_name)."'"; public function sanitiseData($data) { $data = mysql_escape_string($data); return $data; } – srikanth s Feb 14 '13 at 13:22

score 0 · Answer 2 · edited May 23 '17 at 12:12

when executing a query and a value has single quotes, double the single quotes so won't get an error.

SELECT course_id
FROM course_master
WHERE year_id = '6'
AND course_name = '''What is Test?'': Perspectives'

but if you are doing it on PHP, use PreparedStatement on that. This will also prevent from SQL Injection. The article below will show you how to use PHP Extensions.

How to prevent SQL injection in PHP?

score 0 · Answer 3 · answered Feb 14 '13 at 13:10

0

Try this query -

SELECT course_id
FROM course_master
WHERE year_id = 6
AND course_name = '''What is Test?'': Perspectives'

or use escaping -

SELECT course_id
FROM course_master
WHERE year_id = 6
AND course_name = '\'What is Test?\': Perspectives'

answered Feb 14 '13 at 13:10

Devart

119,203
23
166
186

using special characters in mysql query throws exception

3 Answers3