Anyone willing to explain how this works? How long is the effect maintained?

it depends.... what did the spy do?

You need to build a SPY CENTER first to recruit a spy which can perform basic infiltration only( Just click the button below recruits unit). Then build the SPY ACADEMY which allow you to perform advanced infiltration.

*Spy infiltration points << Example u want to perform "Spread Virus", your spy will need to stay on the enemy base for 8 tick(8 points).

They have 9 operations
1) Spread Virus
Damages infantry: Hits Points reduced to 1 in this target.
Spy infiltration points : 8 + 20 Energy

2) Destroy vehicle fuel supplies
Damage vehicle: Takes off 50% of the current HP of all vehicles in this target.
Spy infiltration points : 10 + 30 Energy

3)Destroy heavy vehicles fuel supplies
Damages tanks: Takes off 50% of the current HP of all tanks in this target.
Spy infiltration points : 12 + 40 Energy

4) Lockdown
Prevents any squads/missiles from leaving this target for 3 -6 ticks
Spy infiltration points : 8 + 30 Energy

5) Burn oil reserves
Destroys 50% of the oil reserves in this colony
Spy infiltration points : 48 + 75 Energy

6) EMP Overload
Destroy all energy reserves in this colony
Spy infiltration points : 48 + 120 Energy

7) Boo!
Send a 'Boo!' event to the target colony (Just for fun!)
Spy infiltration points : 4 + 10 Energy

8) ignite thrusters
Sends your spy back to base within 6 ticks from anywhere in the world
Spy infiltration points : 0 + 25 Energy

9) Poison water supplies
Kills 10% of this colony' workers << This the one you mention.
Spy infiltration points : 48 + 100 Energy

One more thing.. To prevent other people doing this thing on you, you will need to "ADD SPY PROTECTION" to protect your base or your outpost. ( JUST CLICK THE BUTTON "ACTIONS" than choose the "ADD SPY PROTECTION".. It cost you 50 energy which protect you for 50 ticks... You can add as many as you can, as long as you have energy.

Hope you clear about it...