Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

PHP

Ignore single quotes stored in a PHP variable

$sql="INSERT INTO tablename(notes) VALUES('$val')";

And the value of $val contains single quotes like Mary's Grade. That's the reason it is not getting stored in the database. How can i ignore single quotes in php variable?

2 Answers

Andrew Shook
Andrew Shook
31,709 Points

Try removing the single quotes wrapping $val in the actual SQL statement.

No its not working...I need something which can escape single quotes inside a variable. I am using PDO connection with MS SQL database.

Andrew Shook
Andrew Shook
31,709 Points

Are you trying to escape a single quote as in " it's " ?

Yes

Andrew Shook
Andrew Shook
31,709 Points

Oh then Hugo Paz 's answer will help you out.

I read something about PDO quote....Is this something which can be of help?

Hugo Paz
Hugo Paz
15,622 Points

Yes you can use PDO::quote. There are a few good example here http://php.net/manual/en/pdo.quote.php

Actually i don't want ' to be converted to ' ' What i want is that ' just gets ignored while inserting it to the database. Example it i want to insert something like "Mary's Grade" in the DB it gets stored as it is.

Hugo Paz
Hugo Paz
15,622 Points

Try this.

 $ps = $db->prepare("insert into yourTable (column) values (:yourValue)");

            $ps->bindValue(":yourValue", $var);

            $ps->execute();

This will insert single quotes on the database.

What is $var here?

Hugo Paz
Hugo Paz
15,622 Points

Should be $val, its the value you want to insert in the database.

So what is :yourValue ?

Hugo Paz
Hugo Paz
15,622 Points

:yourValue is a placeholder for the prepared statement.

You give the placeholder a name, like :placeholder and then you bind the value you want to that placeholder through $ps->bindValue(":yourValue", $var);

$val="here is a quote's and another's ";

Its not inserting it.

Hugo Paz
Hugo Paz
15,622 Points

Can you post all your php code that deals with database please?

Its inserted now....Thanks....!!!

I used "$val" instead of '$val' and it worked.

Hugo Paz
Hugo Paz
15,622 Points

You need to escape it. There are actually a few ways to do it. Check here http://dev.mysql.com/doc/refman/5.0/en/string-literals.html

Please explain with example. How can i escape it in a variable as it changes every time in my application?

I have a little problem when i bind the value for updating the row. My code is below:

$val="here is a quote's and another's hello ";

echo $val;

$db = new PDO("sqlsrv:server=(local);Database=testdb","root","****");

$ps = $db->prepare("UPDATE notes_store SET notes=:placeholder WHERE firstname='sameer' AND course_name='tiff' AND module_name='test' IF @@ROWCOUNT=0 INSERT INTO notes_store(firstname,notes) VALUES('sameer',:placeholder)");

        $ps->bindValue(":placeholder", "$val");

        $ps->execute();