How to escape quotes while inserting to MySQL

Question

I have MySQL table I want to insert data into and wanted to escape quotes and double quotes my current SQL query looks like this

/*a function to escape strings*/
function escape_string($string){
    global $connection;
    return mysqli_real_escape_string($connection, trim($string));
}

$my_post = [
            "post_title" => escape_string("Title Here"),
            "post_content" => escape_string("content")
            "post_excerpt" => substr(escape_string("Summary"),0, 155),
            "post_status" => "publish",
            "post_name" =>  normal_slugify(escape_string($Title)),
            "post_date" => date("Y-m-d h:i:s"),
            "post_date_gmt" => date("Y-m-d h:i:s"),
            "post_modified" => date("Y-m-d h:i:s"),
            "post_modified_gmt" => date("Y-m-d h:i:s")
        ];

    $s_columns = implode(", ",array_keys($post_array));
    $s_values = "'". implode("', '", array_values($post_array)) . "'";
    $query = query("INSERT INTO siri_posts($s_columns) VALUES ($s_values)");

Some content have quotes and double quotes so I need to escape them before I insert them to database right? I tried Using mysqli_real_escape_string() but it's not working and I am getting error when I try to enter title with quotes into the database also the content is user generated so I want I function to escape string before inserting them to database.

I went around the forum and tried a lot of functions but some are depricated and some are not working.

score -2 · Answer 1 · answered Jun 03 '22 at 01:03

-2

Instead of doing this manually, you should be using the parameterized query method that is supported by most database libraries.

$stmt = mysqli_prepare($dbc, "SELECT * FROM users WHERE username = ? AND password = ?");
mysqli_stmt_bind_param($stmt, "s", $userName);
mysqli_stmt_bind_param($stmt, "s", $userPass);
mysqli_stmt_execute($stmt);
$row = mysqli_stmt_fetch($stmt);

Parameter bindings ensure the datatype of the incoming variable, sanitizes the incoming variable (eg. inserts needed escape character on quotes), and is the main way to prevent SQL injection vulnerability in your application

answered Jun 03 '22 at 01:03

Clifford Cheefoon

215
1
8

i don't understand how the query above is related to my question? and what is userName and Password?? are they database username and password? and also SELECT * FROM Users??? i want to insert but?? – Zack Snyder Jun 03 '22 at 01:14
That's very close but still incorrect. – Dharman Jun 03 '22 at 08:23

How to escape quotes while inserting to MySQL

1 Answers1