Home » Questions » Computers [ Ask a new question ]

What do I need to escape when sending a query?

What do I need to escape when sending a query?

"When you execute a SQL query, you have to clean your strings or users can execute malicious SQL on your website.

I usually just have a function escape_string(blah), which:

Replaces escapes (\) with double escapes (\\).
Replaces single quotes (') with an escaped single quote (\').

Is this adequate? Is there a hole in my code? Is there a library which can do this quickly and reliably for me?

I'd like to see graceful solutions in Perl, Java, and PHP."

Asked by: Guest | Views: 34
Total answers/comments: 5
Guest [Entry]

"For maximum security, performance, and correctness use prepared statements.
Here's how to do this with lots of examples in different languages, including PHP:

https://stackoverflow.com/questions/1973/what-is-the-best-way-to-avoid-sql-injection-attacks"
Guest [Entry]

"I would also escape comments (double dash)

--"
Guest [Entry]

"A great thing to use in PHP is the PDO. It takes a lot of the guesswork out of dealing with securing your SQL (and all of your SQL stuff in general). It supports prepared statements, which go a long way towards thwarting SQL Injection Attacks.

A great primer on PDO is included in the book The PHP Anthology 101 Essential Tips, Tricks & Hacks by Davey Shafik etc. 2nd Ed. Makes learning a breeze and is excellent as a reference. I don't even have to think about anything other than the actual SQL Query anymore."
Guest [Entry]

You're better off using prepared statements with placeholders. Are you using PHP, .NET...either way, prepared statements will provide more security, but I could provide a sample.
Guest [Entry]

"In PHP, I'm using this one and I'll appreciate every comment about it :

function quote_smart($valeur)
{
if (get_magic_quotes_gpc())
$valeur = stripslashes($valeur);

if (!is_numeric($valeur))
$valeur = mysql_real_escape_string($valeur);

return $valeur;
}

$IdS = quote_smart($_POST['theID']);
$sql = ""
SELECT * FROM Students
WHERE IdStudent={$IdS};
"";

Needs one more verification if a field can be NULL :

$picture = NULL;
$theidyouwant = 7;
$Name = 'WOOD';

if(is_null($picture))
$p = 'NULL';
else
$p = ""'"".quote_smart($picture).""'"";

$IdS = quote_smart($theidyouwant);

$requete = ""SELECT * FROM Students
WHERE IdStudent={$IdS} AND
PictureStudent={$p} AND
NameStudent='{$Name}';
"";

That's it enjoy !
(hope the post will correctly send underscores and not _ ;)"