Eascaping quotes in mysql query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
spacebiscuit
Forum Contributor
Posts: 390
Joined: Mon Mar 07, 2005 3:20 pm

Eascaping quotes in mysql query

Post by spacebiscuit »

Hi,

how do I escape a sting in an myslq statement:

I tried:

Code: Select all

$query="DELETE FROM `table1` WHERE id=$_POST[\'ID\']";

$query="DELETE FROM `table1` WHERE id=$_POST[\"ID\"]";
I get white space errors with the above.

Thank you.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Eascaping quotes in mysql query

Post by Celauran »

Depends on your database extension. You'd do better to use PDO with prepared statements.

Code: Select all

$query = "DELETE FROM table1 WHERE id = :id";
$stmt = $pdo->prepare($query);
$stmt->execute(array(':id' => $_POST['ID']));
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Eascaping quotes in mysql query

Post by social_experiment »

if you still use mysql look at mysql_real_escape_string(). You should upgrade to prepared statements as Celauran suggests or at least use mysqli functions when interacting with the database.

Code: Select all

<?php
  $id = mysql_real_escape_string($_POST['ID']);
  $sql = "DELETE FROM `table1` WHERE id = $id";  
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
spacebiscuit
Forum Contributor
Posts: 390
Joined: Mon Mar 07, 2005 3:20 pm

Re: Eascaping quotes in mysql query

Post by spacebiscuit »

I found that surrounding the var with curly brackets also worked, is this an acceptiable method?

Thanks.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Eascaping quotes in mysql query

Post by social_experiment »

not if you want to secure the query against injection attacks but i suspect your question wasn't about a security related issue :)
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
spacebiscuit
Forum Contributor
Posts: 390
Joined: Mon Mar 07, 2005 3:20 pm

Re: Eascaping quotes in mysql query

Post by spacebiscuit »

For now it was a question just about how to stop the error from tripping up my script, I will work on the SQL injection stuff next.

Is the mysql_real_escape_string function sufficient to protect against SQL injection attempts?

Thanks.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Eascaping quotes in mysql query

Post by social_experiment »

spacebiscuit wrote:Is the mysql_real_escape_string function sufficient to protect against SQL injection attempts?
i'm going to say no, the url below gives a few things to look at when defending against injection attempts; it's not php specific but the ideas behind the examples can easily be applied in php aswell
https://www.owasp.org/index.php/SQL_Inj ... heat_Sheet
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply