Is there a service that can help me Quote the SQL strings properly?

Question

Hi there,

I'm having a hard time quoting SQL string properly, I want to know if there's a program or a website that can help me quote the string correctly. Where I can enter the SQL strings then the website will analyze and quote it for me. Or maybe a program.. I need it cause I'm having a trouble with them..

Sorry for not providing a detailed information about my question, I have this SQL string. I got it from a book, But I'm having a hard time quoting it properly.

$sql = "INSERT INTO books(title, author, isbn, publisher, year, summary) VALUES (" .$conn->quote($_POST['title']) .
', ' . $conn->quote($_POST['author']) .
', ' . $conn->quote($_POST['isbn']) .
', ' . $conn->quote($_POST['publisher']) .
', ' . $conn->quote($_POST['year']) .
', ' . $conn->quote($_POST['summary']) .
')';

You can see that there are many quotes happening there! I got it from a book, but when it comes a time where I need to devise my own SQL string with the same difficulty like that, maybe I'll split.

Do you have a recommendation where a website or a program can help me escape/quote the strings properly?

Answer 1

Can you be a bit more specific? When writing ADO.NET code, you generally use a SqlParameter object like so, which takes care of all of this stuff automatically:

var cmd = new SqlCommand("select * from foo where fooName = @fooName;", connection);
cmd.Parameters.AddWithValue("@fooName", "O'Reilly is a bloviator");
cmd.ExecuteReader();

Answer 2

The thing that helps me most when writing sql is a good text editor that highlights the text. There are many discussions about which text editor to use. I prefer vi, but I'm positive emacs would do it as well, along with eclipse and just about everything that isn't plain old windows notepad.

Answer 3

Okay, it looks like you're using PHP. You should not manually quote, but rather use prepared statements. Below is one way to do it, using PDO. Other valid syntaxes are given at PDOStatement->execute:

$stmt = $db->prepare("INSERT INTO books(title, author, isbn, publisher, year, summary) VALUES(:title, :author, :isbn, :publisher, :year, :summary)");
$title="Hitchhiker's Guide";
$author="Douglas Adams";
$isbn="0345391802";
$publisher="Del Rey";
$year="1995";
$summary="Arthur Dent accidentally saves the world.";
$stmt->bindParam(":title", $title);
$stmt->bindParam(":author", $author);
$stmt->bindParam(":isbn", $isbn);
$stmt->bindParam(":publisher", $publisher);
$stmt->bindParam(":year", $year);
$stmt->bindParam(":summary", $summary);
$stmt->execute();

Answer 4

Do you have a recommendation where a website or a program can help me escape/quote the strings properly?

You might not need to escape the strings in php if a setting called magic_quotes_gpc is on. You can find out if it is on or not by writing a php file with this code in it:

<?php
    var_dump(get_magic_quotes_gpc());
?>

It will show a bool(true) or bool(false). If it is false then use a function called mysql_real_escape_string to escape. If it is true, you don't need to do anything, your input will be escaped automatically. Remember, don't do both, you dont want to escape twice. Alternatively you could use this function which will find out if you need to escape or not:

<?php
    function clean($input) {
        if (get_magic_quotes_gpc()) {
            // magic quotes are on, no need to escape
            return $input;
        } else {
            // magic quotes are off, we need to escape
            return mysql_real_escape_string($input);
    }
?>

Then just use it like this:

<?php
    $result = mysql_query(sprintf('SELECT * FROM `table` WHERE user="%s"', clean($_POST['user'])));
?>

I'm not able to check the code I've submitted for typos but hopefully...