PHP SQL更新语句转换为（PDO）预处理语句

Question

first of all im new to Stackoverflow and PHP so dont be to hard to me.

Im struggling this week with a sql query:

SET @i=0;UPDATE highscores SET ranking=@i:=(@i+1) ORDER BY highscore DESC;

i use this sql query because i have a table called 'highscores' and columns called highscore and ranking. this code helps me to automatic organize the ranking system(so the highest highscores get rank 1 etc.(no same rank with the same amount of highscore)), but since im using php and want to it be secure with prepared statements of PDO im really confused how to do it. i need some examples how to convert this sql query to a secured pdo prepared statement so code sniffers wont get into the database. this is the code which works:

$stmt = $db->prepare("SET @i=0;UPDATE highscores SET ranking=@i:=(@i+1) ORDER BY highscore DESC;     
$stmt->execute();

but as you see its not secured. i tried with arrays and bindparams but im really confused how to actually get it working while its secured. i hope you guys can help me cuz im struggling with this like 3 days.

Answer 1

Since you are not using any variables from outside of your query this query would be safe. The only thing I would change is removing this structure, because you can receive the ranking easily with a nice SELECT COUNT query.

$query = $pdo->prepare('SELECT COUNT(id) AS count FROM highscores WHERE highscore < :highscore');
$query->execute(['highscore' => $highscore]);
$row = $query->fetch(PDO::FETCH_ASSOC);

$ranking = (intval($row['count']) + 1);

This is a simple example, you are able to order it with IDs after you did checked what comes before this item, on that way you would get unique rankings.