mysqli_real_escape_string doesn't seem to be working

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: %sn", 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: %sn", 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);
?>


WARNING: When using mysqli you should be using parameterized queries and bind_param 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. 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 at 21:45

mysqli

bind_param

$_POST

$_GET

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 at 21:45

mysqli

mysql_query

$db = new mysqli(…)

$db->prepare("…")

mysqli

A lot of problems can be detected and resolved by enabling exceptions in mysqli so mistakes aren't easily ignored.
– tadman
Jun 29 at 21:46

mysqli

Thanks, @tadman! I will try to switch over to Object Oriented PHP
– Emotions
Jun 29 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 at 21:50

1 Answer
1

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

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

i can do this, hold on :)
– Pilan
Jun 29 at 22:02

I'd upvote it again if I could! ⬆︎⬆︎
– tadman
Jun 29 at 22:07

@Emotions If this answered your question you should accept the answer. meta.stackexchange.com/questions/5234/…
– user3783243
Jun 30 at 0:09

I still wasn't able to insert anything, not even a blank row...
– Emotions
2 days ago

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.