Failing insert statement in msql

Question

Below is the section of my code which is causing me problems:

$usertype = $_POST['usertype'];
if ($usertype == "Administration") {

?>

<script type='text/javascript'>
window.onload = promptMessage;

function promptMessage() {

    var x = 38773;

    var code = prompt('Enter the administration code you have been given:', 'Enter code here');

    if (code == x) {
        alert("Administration code accepted");

    } else {
        var secondcode = prompt('The code you have entered is inccorect', 'Enter correct code here or change Usertype');
        if (secondcode == x) {
            alert("Administration code accepted");
        } else {
            location.href = 'AdminCodeFail.html';
        }
    }
}  
</script>
<?php
$con = mysqli_connect("localhost:3306", "root", "***********", "systemone");

$sql = "INSERT INTO completeinfo (FirstName, Surname, UniID, 
                                       HouseNumber, AddressLineOne, AddressLineTwo, City, 
                                       PostCode, County, PhoneNumber, Email, Username, 
                                       Password, UserType)
                                       VALUES
                                       ('$_POST[firstname]','$_POST[surname]','$_POST[uniid]',
                                       '$_POST[housenumber]','$_POST[addresslineone]',
                                       '$_POST[addresslinetwo]','$_POST[city]','$_POST[postcode]',
                                       '$_POST[county]','$_POST[contactnumber]','$_POST[email]',
                                       '$_POST[username]','$_POST[password]','$_POST[usertype]')";

if (!mysqli_query($con, $sql)) {
    die('Error: ' . mysqli_error($con));
} else {
    header("Location:SignUpComplete.html");
}

The problem I'm having is that the insert query is just not working. The query fails to insert any data into the database and I am at a loss as to why. The connection to the database is working fine and I'm receiving no errors when testing the query itself. So why isn't the query functioning?

echo $sql ; and copy the same query in mysql and see what u get !! — Abhik Chakraborty, Mar 10 '14 at 17:04
two things... first i think you need to make a variable equal to your $_POST['firstname'] and then pass that variable into your mysql select (im not a php expert, but thats what ive done and it works). Second you should try the exact statement in your database ide and see if the query works before trying to get it to work in php — John Ruddell, Mar 10 '14 at 17:06
session_start(); include('/include/connection.php'); $user = $_POST['user']; $pass = $_POST['pass']; $query= "SELECT password FROM FB_User WHERE username ='$user'"; $sql_pass=mysql_query($query); $results=mysql_fetch_row($sql_pass); ...............let me know if that helps — John Ruddell, Mar 10 '14 at 17:08
You really should switch to prepared statements; a `'` character in one of your variables will break your query or worse. Also note that your query will only run when `$_POST['usertype'] == "Administration"`. — jeroen, Mar 10 '14 at 17:10
And putting a security code in the javascript source code makes it kind of useless... — jeroen, Mar 10 '14 at 17:15

score 1 · Accepted Answer · answered Mar 10 '14 at 17:11

1

Add

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

after your code and it will give you more descriptive errors as to why the query is failing.

answered Mar 10 '14 at 17:11

Brandon Kiefer

329
2
9

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

You can't have array variables in double quotes like this:

$string = "hello $array['index'] world!";

They must be:

$string = "hello {$array['index']} world!";

Your code has SQL injection vulnerabilities up the wazoo. I strongly suggest reading: How can I prevent SQL injection in PHP?

edited May 23 '17 at 10:26

Community

1
1

answered Mar 10 '14 at 17:16

Sammitch

30,782
7
50
77

score 0 · Answer 3 · edited May 23 '17 at 12:21

0

How should I write PHP $_POST vars in a mysql_query function?

this is your error and this type of question has already been answered.

use

. mysql_real_escape_string to pop out of the string and recognize a value

LOOK AT THE LINK... it will help :)

edited May 23 '17 at 12:21

Community

1
1

answered Mar 10 '14 at 17:17

John Ruddell

25,283
6
57
86

Whether you use the $_POST variable directly or put it into another variable you should escape it (using mysql_real_escaape_string or the like). – Kickstart Mar 10 '14 at 17:32
there will be no errors if you put it in a variable or I should say that functionality is handled with a variable. I'm not a programming expert or anything so that my be the better practice. but to use a $_POST literal value you have to escape out like the link I posted – John Ruddell Mar 10 '14 at 17:35
No you do not. It is quite legitimate to have used a $_POST variable in the way the OP has done (ie, used it as a normal array, just without quotes around the subscript), just that it is wide open to SQL injection. Moving it to a variable doesn't change this problem as it would still be wide open to SQL injection, it just gives you a convenient line to use mysql_real_escape_string. – Kickstart Mar 10 '14 at 17:39
I get what you're saying, maybe using the word have to is not the best choice of words.. but i was saying that it would be best to use mysql_real_escape_string ... did you read the answer given by Alix Axel in the link I posted? – John Ruddell Mar 10 '14 at 17:45
Yes, but my point is that it is not an either / or situation. Whether you put the field directly into the SQL or move it to a variable first is irrelevant to whether you need to escape it to prevent SQL injection. `$query= "SELECT password FROM FB_User WHERE username ='$user'";` is wide open to SQL injection in the same way that `$query= "SELECT password FROM FB_User WHERE username ='$_POST[user]'";` is wide open, and both are just as valid syntax wise. – Kickstart Mar 10 '14 at 17:49
yup... like i was saying i know very little about php and only had to use it once where i didn't care about sql injection (school project). the link I posted was to answer the question though :) – John Ruddell Mar 10 '14 at 17:51
Sorry to labour the point but while the original code is wide open to SQL injection, nothing to say that this is why it is failing as it stands (and your example code is equally wide open). – Kickstart Mar 10 '14 at 18:03
ok so the link i posted.. the OP in that question was having the exact same issue while using $_POST[''] in his sql select. Alix Axel answered that and said to use mysql_real_escape_string of the $_POST... to fix the error and also guard against sql injection. so since the OP used the same syntax as the error the other OP had I assumed that this was his error... If its not i'll remove my post, but thats what I figured his error was. – John Ruddell Mar 10 '14 at 18:18

Failing insert statement in msql

3 Answers3