mysqli_real_escape_string doesn't seem to be working

Question

I am a novice in PHP. I am trying to insert a variable's value into a MariaDB table and was trying to use mysqli_real_escape_string to escape '$value'. I got the idea from here. It inserted an empty string to the table(I did add a connection link to the database).

So, I copied and pasted the following code from PHP Manual, it still didn't work. The output I got was an error code alone: Error: 42000. What am I missing?

I am using a Virtualbox, OS: CentOS7

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

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

mysqli_query($link, "CREATE TEMPORARY TABLE myCity LIKE City");

$city = "'s Hertogenbosch";

/* this query will fail, cause we didn't escape $city */
if (!mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
    printf("Error: %s\n", mysqli_sqlstate($link));
}

$city = mysqli_real_escape_string($link, $city);

/* this query with escaped $city will work */
if (mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
    printf("%d Row inserted.\n", mysqli_affected_rows($link));
}

mysqli_close($link);
?>

Update: Thank you for your prompt response! I tried @Pilan's code but I was not able to insert a row. I created a table in the database called 'City'. I checked whether there was a database connection in the code and it did return "Connected". Here is the updated code:

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

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

else {

    echo "Connected";

$city = "'s Hertogenbosch";    

// Connect to db, returns mysqli-connection
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

// Prepare, "?" for placeholders, returns mysqli-statement
$stmt = $mysqli->prepare("INSERT INTO City (Name) VALUES (?)");

// Bin param to statement, with type "s" for string
$stmt->bind_param("s", $city);

//Execute
/* this query with escaped $city will work */
if ($stmt->execute()) {
    printf("%d Row inserted.\n", mysqli_affected_rows($link));
}

}
mysqli_close($link);
?>

Update: Thanks guys, The code worked, It did insert into the table but 'Row inserted' didn't show up: turns out, I forgot to take out the semicolon from 'execute()' inside if conditional statement.

**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Jun 29 '18 at 21:45
Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. — tadman, Jun 29 '18 at 21:45
A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so mistakes aren't easily ignored. — tadman, Jun 29 '18 at 21:46
If you're just getting started with PHP and want to build applications, I'd also strongly recommend looking at various [development frameworks](http://codegeekz.com/best-php-frameworks-for-developers/) to see if you can find one that fits your style and needs. They come in various flavors from lightweight like [Fat-Free Framework](https://fatfreeframework.com/) to far more comprehensive like [Laravel](http://laravel.com/). These give you concrete examples to work from and much stronger guidance on how to write your code and organize your files. — tadman, Jun 29 '18 at 21:46
Thanks, @tadman! I will try to switch over to Object Oriented PHP — Emotions, Jun 29 '18 at 21:49
The most important thing, above all else, is placeholder values. The OO-style is just a lot more succinct and I think once you get used to it you'll find it's considerably less work to use. — tadman, Jun 29 '18 at 21:50
If error is coming from an example in the PHP manual, make a comment there. — Rick James, Jun 30 '18 at 17:42
@RickJames The error message I got was Error: 42000, instead of Error: 42000. and 'Row inserted'. — Emotions, Jul 01 '18 at 03:12

SirPilan · Accepted Answer · 2018-06-29T22:02:18.913

6

Here you got an example of a prepared statement:

$city = "'s Hertogenbosch";    

// Connect to db, returns mysqli-connection
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

// Prepare, "?" for placeholders, returns mysqli-statement
$stmt = $mysqli->prepare("INSERT INTO myCity (Name) VALUES (?)");

// Bin param to statement, with type "s" for string
$stmt->bind_param("s", $city);

// Well execute :D
$stmt->execute();

For details have a look here: prepare, bind

edited Jun 29 '18 at 22:02

answered Jun 29 '18 at 22:00

SirPilan

4,649
2
13
26

2

Great example, but always best if it's adapted to the requirements of the original question. – tadman Jun 29 '18 at 22:02
1

i can do this, hold on :) – SirPilan Jun 29 '18 at 22:02
@Emotions If this answered your question you should accept the answer. https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – user3783243 Jun 30 '18 at 00:09
Awesome example of prepared statements @Pilan! – Emotions Jul 01 '18 at 03:27
@Emotions please post what you've tried - its easier to help from there – SirPilan Jul 01 '18 at 08:47

mysqli_real_escape_string doesn't seem to be working

1 Answers1