PHP MySql Real Escape String not working

Question

I am trying to read and write to a database. Here is the code I have so far:

$mysql = mysqli_connect("example.com", "johndoe", "abc123"); // replace with actual credidentials
$username = mysqli_real_escape_string("username");
$sql = "CREATE DATABASE IF NOT EXISTS dbname";
if (!mysqli_query($mysql, $sql)) {
    echo "Error creating database: " . mysqli_error($mysql);
}
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
mysqli_close($mysql);
$mysql = mysqli_connect("example.com", "johndoe", "abc123", "dbname"); // replace with actual credidentials
$sql = "CREATE TABLE IF NOT EXISTS Users(ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), username CHAR(15), password CHAR(15), email CHAR(50))";
if (!mysqli_query($mysql, $sql)) {
    echo "Error creating table: " . mysqli_error($mysql);
}
$sql = "INSERT INTO Users(username, password, email) VALUES(" . $username . ", " . $password . ", " . $email . ")";
if (!mysqli_query($mysql, $sql)) {
    echo "Error: " . mysqli_error($mysql);
}
mysqli_close($mysql);

However, when I try to run it, it has an error:

Error: 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 ' , )' at line 1

Could anybody tell me how to fix this?

Would like to, but where is there a MySQL_real_escape_string in this program? — David
– David, Commented Feb 28, 2014 at 5:26

user1844933 · Accepted Answer · 2014-02-28 05:39:04Z

1

mysqli_real_escape_string requires connection parameter too...

$username = mysqli_real_escape_string($mysql,"username");

answered Feb 28, 2014 at 5:39

user1844933

3,4132 gold badges28 silver badges44 bronze badges

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

Comments

inf3rno · Accepted Answer · 2014-02-28 05:26:35Z

0

Use preapred statements: http://hu1.php.net/mysqli_prepare they automatically escape the params...

Btw. the params in your last sql are not properly escaped and concatenated.

answered Feb 28, 2014 at 5:26

inf3rno

26.3k13 gold badges122 silver badges208 bronze badges

Comments

Shankar Narayana Damodaran · Accepted Answer · 2014-02-28 05:31:56Z

0

Rewrite your second SQL query like this..

$sql = "INSERT INTO Customers(`username`, `password`, `email`) VALUES ('$username','$password','$email')";

The problem was there was improper escaping.

Sidenote: Switching to PreparedStatements is even more better to ward off SQL Injection attacks !

edited Feb 28, 2014 at 5:31

answered Feb 28, 2014 at 5:25

Shankar Narayana Damodaran

68.6k43 gold badges102 silver badges129 bronze badges

1 Comment

Shankar Narayana Damodaran Over a year ago

@inf3rno, I agree. Sidenote added.

NoobEditor · Accepted Answer · 2014-02-28 05:53:40Z

0

Escaping characters and making them clean before inserting in database, you can use below function to santizie them properly,

<?php
function cleanInput($input) {

  $search = array(
    '@<script[^>]*?>.*?</script>@si',   // Strip out javascript
    '@<[\/\!]*?[^<>]*?>@si',            // Strip out HTML tags
    '@<style[^>]*?>.*?</style>@siU',    // Strip style tags properly
    '@<![\s\S]*?--[ \t\n\r]*>@'         // Strip multi-line comments
  );

    $output = preg_replace($search, '', $input);
    return $output;
  }
?>

and finally when output, to escape ' on page, use :

htmlspecialchars($quote_str, ENT_QUOTES); or htmlentities($quote_str, ENT_QUOTES);

answered Feb 28, 2014 at 5:53

NoobEditor

16k20 gold badges85 silver badges117 bronze badges

Collectives™ on Stack Overflow

PHP MySql Real Escape String not working

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related