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 = "[email protected]";

    $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";
    }
?>

Just because you're using ext/mysql (it might not have been deprecated in v5.3, but its use was certainly discouraged) doesn't mean you should avoid escaping your variables. Your code is wide open to SQL injection and XSS attacks. — eggyal
– eggyal, Commented Jul 15, 2013 at 23:28

spencer7593 · Accepted Answer · 2013-07-15 23:52:20Z

3

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,[email protected],5551212) 

... VALUES ('paul','[email protected]','5551212')

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

... VALUES ('O'Reilly','[email protected]','5551212')

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

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

edited Jul 15, 2013 at 23:52

answered Jul 15, 2013 at 23:42

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RedJax Over a year ago

I've now added this, to no effect, any other suggestions

spencer7593 Over a year ago

If you aren't getting querytext echoed out, that can really only mean that that line of code is not being executed, so you've got a control flow issue in reaching the statement on that line. This is just normal debugging. Add echo statements following every conditional, to see where your logic is taking you.

RedJax Over a year ago

I think it's to do with the JS code that is linked with it as to why it won't echo

RedJax Over a year ago

I have just added this to the email which is being send and the result is that the values are appearing VALUES ( , , )

samayo · Accepted Answer · 2013-07-16 00:14:50Z

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';
        }

edited Jul 16, 2013 at 0:14

answered Jul 15, 2013 at 23:34

samayo

16.5k13 gold badges95 silver badges115 bronze badges

12 Comments

RedJax Over a year ago

That one works... Its not that query that is the issue, its the query that I posted above the entire file.

samayo Over a year ago

Well, just to do the same and add or die(mysql_error()) to it. That was the point I was trying to make.

samayo Over a year ago

Well, that is weird, anyway, try my updated answer. just copy/paste

RedJax Over a year ago

Yeah its exactly the same, still no change.

samayo Over a year ago

Ok, again edited. but, you are doing something silly somewhere, because I can't find any more fault here

|

cocogorilla · Accepted Answer · 2013-07-15 23:42:23Z

1

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']."'
        )");

answered Jul 15, 2013 at 23:42

cocogorilla

1,86518 silver badges36 bronze badges

3 Comments

RedJax Over a year ago

The apostrophes are already there and eliminating the comma didn't help

cocogorilla Over a year ago

In your original post, the apostrophes are clearly not there and there is an extra comma. I don't think you can be helped.

RedJax Over a year ago

I do apologise, I was looking at my current code, which I had not edited then and the apostrophes where there.

Collectives™ on Stack Overflow

MySQL PHP Query Issues

3 Answers 3

4 Comments

12 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

12 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related