MySQL PHP Query Issues

Question

I need some help with my MySQL Queries in PHP

Before I say anymore I use mysql_* function, I know they are depreciated, however only from PHP v5.5 and I have PHP 5.3 installed on my server and other mysql_* functions are working its just this one.

I'm trying to insert values from a form into a table on form submit, the code is in the correct place as the PHP sends the email and echo's the 'mail sent'.

This is my query:

mysql_query("INSERT INTO customers (
    name,email,telephone
) VALUES (
    ".$_POST['name'].",
    ".$_POST['email'].",
    ".$_POST['telephone'].",
)");

This is the document it's in:

<?php

    // Database connect
    $db_host        = 'localhost';
    $db_user        = 'redjaxco';
    $db_pass        = 'CORRECT PASSWORD';
    $db_database    = 'redjaxco_website'; 

    $link = mysql_connect($db_host,$db_user,$db_pass) or die('Unable to establish a DB connection');

    mysql_select_db($db_database,$link);
    mysql_query("SET names UTF8");

    $owner_email = "contact@red-jax.com";

    $headers = 'From:' . $_POST["email"];
    $subject = 'Online Form - '. $_POST["topic"]. " : " . $_POST["name"];
    $messageBody = "";

    if($_POST['topic']!='nope'){
        $messageBody .= '<p>Subject: ' . $_POST["topic"] . '</p>' . "\n";
        $messageBody .= '<br>' . "\n";
    }
    if($_POST['name']!='nope'){
        $messageBody .= '<p>Visitor: ' . $_POST["name"] . '</p>' . "\n";
        $messageBody .= '<br>' . "\n";
    }
    if($_POST['email']!='nope'){
        $messageBody .= '<p>Email Address: ' . $_POST['email'] . '</p>' . "\n";
        $messageBody .= '<br>' . "\n";
    }else{
        $headers = '';
    }
    if($_POST['phone']!='nope'){        
        $messageBody .= '<p>Phone Number: ' . $_POST['phone'] . '</p>' . "\n";
        $messageBody .= '<br>' . "\n";
    }
    if($_POST['message']!='nope'){
        $messageBody .= '<p>Message: ' . $_POST['message'] . '</p>' . "\n";
    }

    if($_POST["stripHTML"] == 'true'){
        $messageBody = strip_tags($messageBody);
    }

    try{
        if(!mail($owner_email, $subject, $messageBody, $headers))
        {
            throw new Exception('mail failed');
        }
        else
        {
            mysql_query("INSERT INTO customers (
                name,email,telephone
            ) VALUES (
                ".$_POST['name'].",
                ".$_POST['email'].",
                ".$_POST['telephone'].",
            )");

            echo 'mail sent';
        }
    }catch(Exception $e){
        echo $e->getMessage() ."\n";
    }
?>

Answer 1

Not that I enjoy saying this, but I would tell you to add, error handling i.e or die(mysql_error()) to your query like:

mysql_select_db($db_database,$link) or die(mysql_error());
mysql_query("SET names UTF8")or die(mysql_error());

I don't even know what the second query does, but adding error handling will give you an understandable error that you can work at, at-least.

EDIT

 mysql_query("INSERT INTO customers (
                name,email,telephone
            ) VALUES (
                '".$_POST['name']."',
                '".$_POST['email']."',
                '".$_POST['telephone']."'
            )") or die(mysql_error());

EDIT-2

Ok, you probably have errors turned of by default, so write this at the top of your scripts.

error_reporting(E_ALL);
ini_set('display_errors', '1');

EDIT-3

 if(!mail($owner_email, $subject, $messageBody, $headers))
        {
            echo 'Email not sent';
        }
        else
        {
        mysql_query("INSERT INTO customers ( name, email, telephone 
                        ) VALUES (
                        '".$_POST['name']."',
                        '".$_POST['email']."',
                        '".$_POST['telephone']."')
                    ") or die(mysql_error());

            echo 'mail sent';
        }

Answer 2

For debugging, the usual pattern is to print out the SQL text before you send it to the database, something like this:

$querytext = "INSERT INTO customers (
            name,email,telephone
        ) VALUES (
            '".mysql_real_escape_string($_POST['name'])."',
            '".mysql_real_escape_string($_POST['email'])."',
            '".mysql_real_escape_string($_POST['telephone'])."',
        )";
echo "querytext=" . $querytext;  // display statement for debugging
mysql_query($querytext) or die(mysql_error());

Check whether the statement actually succeeded or not, and if it didn't, get some output you can work with, rather than pulling a Dr.Evil pinky-to-the-corner-of-the-mouth "I just assume it will all go to plan. What?"

And PLEASE do yourself a BIG favor and sanitize those inputs, something like this:

    mysql_real_escape_string($_POST['name'])

---

To answer your question, the most likely reason your query is failing is that the string literals in your statement are not enclosed in quotes...

compare:

... VALUES (paul,paul@foo.com,5551212) 

... VALUES ('paul','paul@foo.com','5551212') 

And sanitize those inputs, to deal with names like 'O'Reilly

... VALUES ('O'Reilly','ore@me.org','5551212')

and more disturbingly named customers, like Little Bobby Tables...

"Robert','',''); DROP TABLE customers; -- "

Answer 3

You need to enclose your strings in apostrophes and eliminate the comma at the end of your values list:

mysql_query("INSERT INTO customers (
            name,email,telephone
        ) VALUES (
            '".$_POST['name']."',
            '".$_POST['email']."',
            '".$_POST['telephone']."'
        )");