PHP MySQL prepared statement: procedural Insert / update example

Question

I struggle to understand prepared statements in PHP to allow users to edit a MySQL-database. The user input is UTF-8, typical examples are the name in Arabic, Chinese, ... It also generates a problem when using Geo-location as 47°23'15"N, 4°12°27"E, as is visible i.e. in Wikipedia.

Best lead I found to my problem to insert, insert ignore, on duplicate key ... to update datasets in a database from user-input using prepared statements. An interesting lead is in PHP Insert Prepared Statement, but that's "PDO", which I happen to lack any experience with. So far I tried to sanitize, now it seems to me that it might be far easier and safer to use prepared statements. But. I'm bloody amateur. And use procedural statements. And never tried a prepared statement before. And the input expects better understanding.

So I would like to take something like

<?php
$name = $_POST['name'];
$user = filter_var($_POST['user'], FILTER_SANITIZE_EMAIL);
$descr = $_POST['utf8text'];
$geo = $_POST['geo']; // See [1] below.
?>

[1] Which is i.e. 47°23'15"N, 4°12'27"E and I am not sure how to properly escape it? filter_var($_POST['geo'],FILTER_SANITIZE_ADD_SLASHES) returns 47°23\'15\"N, 4°12\'27\"E?

Then to enter this into an SQL database like traditional

<?php
$link = mysqli_connect('localhost', 'user', 'pass','database');
mysqli_set_charset($link,'utf8');
$insertsql = "INSERT INTO `database` (`name`,`user`,`descr`, geo) 
   VALUES ('". $name . "', '" . $user . "', '" . $descr . "', " . $geo . "') 
   ON DUPLICATE KEY UPDATE `descr`='" . $descr . "', '" . $geo . "';
mysqli_query($link,$insertsql);
?>

Okayokay, using added code to make sure the database connection works, the query is processed properly and failure handling. But I want to simplify.

My question would be, how I would prepare such group of values for a prepared statement. And I believe to understand I must replace mysqli_query with mysqli_prepare and I need a count of the fields addressed and use "ssss" but don't find any explanation in any of the manuals what the s's (multiple "s") do.

What I tried didn't work (yet) and I need a working example to understand what I have to do. I tried to adjust the example in above linked article without PDO to no success. And I am worried if/how the prepared data is transferred in strings when using ON DUPLICATE KEY, as I don't find any explanation there either (PEBKAS?).

Any help appreciated!

one of your problems could stem from the charset that must be utf8mb4, not utf8 — Your Common Sense, Apr 07 '22 at 07:34
What is the datatype of `geo`? If it is `VARCHAR`, it would accept `47°23'15"N, 4°12'27"E`. If it is `POINT` or anything else, it would need to be converted. — Rick James, Apr 08 '22 at 23:00
@Dharman there is no content on the dupe target which demonstrates procedural syntax. Please rehammer. — mickmackusa, May 05 '23 at 18:28

score 0 · Answer 1 · answered Apr 06 '22 at 18:09

If I understood correctly, you want to make a mysql prepared query? (I apologize if I misunderstood the problem)

But here my solution and explanation. First :

$stmt  = mysqli_prepare($link, "INSERT INTO `database` (`name`,`user`,`descr`, geo) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE descr= ?, ?;");

Here $link is your mysqli_connect

You set ? where you want to put your future variables

mysqli_stmt_bind_param($stmt, 'ssssss', $name, $user, $descr, $geo, $descr, $geo);

All ? need to be defenied what types of variable it's gonna be, here it's gonna be a string so we set S (You can find the others possibilities for the second parameter here)

Then all ? will be bound to your variables in the order in which you want it to appear instead of ?

mysqli_stmt_execute($stmt)

And finally you execute the query

PHP MySQL prepared statement: procedural Insert / update example

1 Answers1