Getting an error of SQl Syntax

Question

Getting an error of

"Error: 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1"

Please help guys, Many thanks in advance.

Code:

<?php 
include('head.php');
if(isset($_POST['submit'])) 
{
    $userid = trim($_POST['userid']);
    $email  = trim($_POST['email']);
    $mobile = trim($_POST['mobile']);

    $sql = mysqli_query($conn,"INSERT INTO forgot(userid,email,mobile)VALUES ('$userid','$email','$mobile')");

    if (mysqli_query($conn,$sql)) 
    {
        echo "We will Contact you Soon.<br>";
    } 
    else 
    {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }
}
?>

<!DOCTYPE HTML>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>******</title>
    <link href="forum-styles.css" rel="stylesheet" type="text/css">
</head>
<style type="text/css">

    .txtField {
        padding: 5px;
        border:#fedc4d 1px solid;
        border-radius:4px;
    }
</style>
<body background="img/gold-and-money.jpg">
    <form action="" method="post" class="basic-grey">
        <h1>****** Forgot Password
            <span>Please let us know your UserId, We will reset password and inform you.</span>    </h1>
            <label>
                <span>User Id :</span>
                <input type="text" name="userid" required />
            </label>
            <label>
                <span>Mobile N. :</span>
                <input type="text" name="mobile"  required/>
            </label>
            <label>
                <span>Email Id :</span>
                <input type="text" name="email"  required/>
            </label>
            <label>
                <div align="right"><span>&nbsp;</span> 
                    <input type="submit" class="button" value="Submit"  name="submit"/>
                </div>
            </label>
        </form>
    </body>
    </html>

You're running your query twice. The first time, you're assigning the result to `$sql`; the second time, you're trying to run that result as a query. — andrewsi, Dec 12 '14 at 04:17
@AnkitBhatanagar Never mind. Andrew hit the nail on the head. You're running `mysqli_query` twice. — Funk Forty Niner, Dec 12 '14 at 04:19
@Fred-ii- - to be fair, he should also be escaping his data, or using bound parameters. — andrewsi, Dec 12 '14 at 04:20
@andrewsi Yes indeed, he should. I suggest you post your comment as an answer. I'd be happy to +1 — Funk Forty Niner, Dec 12 '14 at 04:21
Your present code is open to [**SQL injection**](http://stackoverflow.com/q/60174/). [**Use `mysqli` with prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or [**PDO with prepared statements**](http://php.net/pdo.prepared-statements), *they're much safer*. Look into those links and further your research on the Web for tutorials for those related topics. — Funk Forty Niner, Dec 12 '14 at 04:26
@AnkitBhatanagar Please consider accepting Andrew's answer, it's detailed and he was the one that found the problem originally. *cheers* — Funk Forty Niner, Dec 12 '14 at 04:34

score 2 · Answer 1 · edited Dec 12 '14 at 04:32

$sql = mysqli_query($conn,"INSERT INTO forgot(userid,email,mobile)VALUES ('$userid','$email','$mobile')");

if (mysqli_query($conn,$sql)) 
{
    echo "We will Contact you Soon.<br>";
}

You've got two calls here to mysqli_query. The first time, you're making the query and assigning the return value to $sql; the second time, you're running $sql as a query.

To fix the immediate problem, do something along the lines of:

$sql = "INSERT INTO forgot(userid,email,mobile)VALUES ('$userid','$email','$mobile')";

if (mysqli_query($conn,$sql)) 
{
    echo "We will Contact you Soon.<br>";
}

You're assigning your query to a string, and then using that in your query. This makes debugging things easier, as you can now output your generated query to check what you're producing.

However

You're also passing user-generated data directly into an SQL query, without escaping it. This is very bad - at best, you're going to have a problem if some of the data contains apostrophes. At worst, your database will get hacked. One solution here is to use escaping, as Fred suggested, using mysqli_real_escape_string:

$userid = mysqli_real_escape_string($conn, $_POST['userid']);
$email  = mysqli_real_escape_string($conn, $_POST['email']);
$mobile = mysqli_real_escape_string($conn, $_POST['mobile']);

I'd suggest also looking at using bound parameters and a prepared statement instead, for added extra security.

Use prepared statements, or PDO with prepared statements, they're much safer.

I'm a man of my words. Good show and well-deserved upvote and then some ;) — Funk Forty Niner, Dec 12 '14 at 04:29
Essays do have their good side. Did you see all the other answers pouring in, feeding off your comment? lol too funny. A few have been deleted. — Funk Forty Niner, Dec 12 '14 at 04:30
Eh, I don't mind that - I'm happy to help fix problems, and am generally slightly too lazy to post an answer instead of a comment. — andrewsi, Dec 12 '14 at 04:34
I know how you feel. I had a long run of "commenting only". But I too enjoy helping, plus it increases my experience. — Funk Forty Niner, Dec 12 '14 at 04:36

score 0 · Answer 2 · answered Dec 12 '14 at 04:21

0

@andrewsi answered correct: "You're running your query twice. The first time, you're assigning the result to $sql; the second time, you're trying to run that result as a query."

answered Dec 12 '14 at 04:21

Ankit Bhatanagar

45
1
11

score 0 · Answer 3 · answered Dec 12 '14 at 04:28

@andrewsi, you r running your query twice and your your query contains variables which you have make them as literals. so code would be like this:

$sql ="INSERT INTO forgot(userid,email,mobile)VALUES ($userid,$email,$mobile)";

    if (mysqli_query($conn,$sql)) 
    {
        echo "We will Contact you Soon.<br>";
    } 
    else 
    {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }
}

I hope this will help you.

score 0 · Answer 4 · answered Dec 12 '14 at 04:33

Here is a basic example. Check where you have a turn. Always keep follow the standard way of coding.

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->query("CREATE TABLE myCity LIKE City");

$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
$mysqli->query($query);

printf ("New Record has id %d.\n", $mysqli->insert_id);

/* drop table */
$mysqli->query("DROP TABLE myCity");

/* close connection */
$mysqli->close();
?>

score 0 · Answer 5 · answered Dec 12 '14 at 04:44

Ankit, their are few things to take care of while coding the queries, instead of explaining them, I will try to rewrite the query:

$query = sprintf("INSERT INTO forgot('userid','email','mobile')
                            VALUES ('%s', '%s', '%s')"
              , mysqli_real_escape_string( $con, $_POST['userid'] )
              , mysqli_real_escape_string( $con, $_POST['email'] )
              , mysqli_real_escape_string( $con, $_POST['mobile'] ));

if (mysqli_query($dbConnection, $query)) {
   echo "Successfully inserted" . mysqli_affected_rows($conn) . " row";
} else {
    echo "Error occurred: " . mysqli_error($dbConnection);
}

if in case, userid is the integer, convert the userid to int as follows before creating the $query:

$userid = (int)$_POST['userid'];

score 0 · Answer 6 · edited Dec 12 '14 at 05:19

0

$sql = "INSERT INTO forgot(userid,email,mobile)VALUES ('$userid','$email','$mobile')";

if (mysqli_query($conn,$sql)) 
{
    echo "We will Contact you Soon.<br>";
} 
else 
{
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

It will work.

edited Dec 12 '14 at 05:19

Kampai

22,848
21
95
95

answered Dec 12 '14 at 04:57

Onion

31
1
1
3

Getting an error of SQl Syntax

6 Answers6