Minus to minus gives ...

From the school course of arithmetic, everyone knows that a minus to a minus gives a plus. Your humble servant also all his life was confident in this seemingly unshakable axiom. But the other day, an event occurred that turned the worldview around and made me look at the usual things with a new look.

In the process of developing administrative tools for the trellis club, a function was needed to anullate all the results of a particular game. It would seem that it could be easier. We change the status of the game, roll back the denormalization data with player statistics, disable the operational caches affecting this data, and the trick is done. But PostgreSQL and psycopg2 have their own opinion on this subject, which does not coincide with the opinion of the editorial board.


')
Simplified problem query looked like this:

cursor.execute("update pref_player set games=games-1, rating=rating-%s where player_id=%s", (rating, player_id))

That is, we need to decrement the total number of games and to annul the player rating accumulated in this game. The request has been parameterized. Screened by regular psycopg2 products. It seems there is simply nowhere to be mistaken. But in fact, if the rating is negative, this query will turn into:

update pref_player set games=games-1, rating=rating

That is, an explosion occurs, the base breaks into the intestine, and hereinafter. In other words, the base happily took two consecutive minuses as a comment, and beat the data into the cabbage. And the driver psycopg2 missed all this joy without a single exclamation. Although, I could probably determine that there is a comment in the request and the number of parameters to be escaped is not true.

It is treated quite simple. It is enough to say, for example, rating = rating - (% s) instead of rating = rating-% s and everything will work as it should, But the fact of unconditional trust in the processing of parameterized queries by database drivers should be questioned.

Take care of yourself, be careful, and may the force be with you.