2

My text forms won't allow single " ' " to occur in the input fields. I get an sql syntax error. Is there any good way to allow single apostrophes to be allowed in my text field?

here's my code

html 

   <input class='what' type='text' name='one' required>
    <textarea name='two' required></textarea>
    <input type='submit'>
    </form>

My database

  // Create connection
    $conn = mysqli_connect($servername, $username, $password, $dbname);
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $sql = "INSERT INTO whatsgood (one, two)
    VALUES ('$one', '$two')";

    if (mysqli_query($conn, $sql)) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }

    mysqli_close($conn);
    ?>

any help would be very appreciated. Thank you!

kenny
  • 438
  • 4
  • 14
  • Use addslashes() function for values in query. – Nisarg Dec 04 '15 at 04:42
  • 3
    Possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – chris85 Dec 04 '15 at 04:56
  • 1
    what you could also do instead of what these guys are saying is put prepared statement instead of what you currently have that would also make the query less vulnerable to SQL injections as @chris85 mentioned – BRoebie Dec 04 '15 at 08:12
  • 1
    I have submitted an answer that makes your page less vulnerable and does the escaping for you use it please better safe then sorry mate. also thanks to @chris85 I didn't even realize it at first that his code was vulnerable. – BRoebie Dec 04 '15 at 08:59

3 Answers3

5

Use addslashes PHP function (It Quote string with slashes)

$sql = "INSERT INTO whatsgood (one, two) VALUES ('".addslashes($one)."', '".addslashes($two)."')";

Example:

<?php
$str = "Is your name O'Reilly?";

// Outputs: Is your name O\'Reilly?
echo addslashes($str);
?>

You can also use mysqli_real_escape_string (Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection)

$sql = "INSERT INTO whatsgood (one, two) VALUES ('".mysqli_real_escape_string($conn,$one)."', '".mysqli_real_escape_string($conn,$two)."')";
Thamilhan
  • 13,040
  • 5
  • 37
  • 59
  • Thanks so much. This is perfect. I have 6 minutes left until I can accept your answer, but I will be sure to do so! – kenny Dec 04 '15 at 04:45
  • 1
    Don't use the addslashes function. Look at the addslashes manual's entry. `To escape database parameters, DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_literal(), pg_escape_string() for PostgreSQL) should be used for security reasons.` http://php.net/manual/en/function.addslashes.php OP should use parameterized queries. – chris85 Dec 04 '15 at 04:55
  • @kenny I suggest you to use `mysqli_real_escape_string` method as do @chris85 says. – Thamilhan Dec 04 '15 at 04:59
2

Use prepared statements this is much safer against SQL-Injections than just escaping.

Change this:

$sql = "INSERT INTO whatsgood (one, two)
VALUES ('$one', '$two')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

To this:

$stmt = $conn->prepare("INSERT INTO whatsgood(`one` , `two`) VALUES ( ? , ? )");

$stmt->bind_param("ss", $one , $two);

if($stmt->execute()){
echo "New record created successfully";
}
else{
echo "Error: " . $stmt->error;
}
BRoebie
  • 374
  • 3
  • 13
0

Use mysqli_real_escape_string during INSERT to escape values.

$sql = "INSERT INTO whatsgood (one, two)
VALUES ('".mysqli_real_escape_string($conn, $one)."', '".mysqli_real_escape_string($conn, $two)."')";
Samir Selia
  • 7,007
  • 2
  • 11
  • 30