Camacho collected Nick Evans’s chip before scoring the only try of the game to close Quins’ deficit to a single point before Evans converted to complete an incredible turnaround.
Quins had been second best for long periods and looked to be heading to defeat when Rodriguez landed a monstrous 40-metre drop goal seven minutes from time.
But Quins’ never-say-die attitude saw them through and has given them a record third triumph in European club rugby's second-tier tournament, and a place in next season’s Heineken Cup.
Stade Francais were looking to end their wretched run of form with an end of season flourish at Cardiff City stadium.
But, in a game that saw only one try, the consistent kicking of Evans ensured that Quins had a 9-6 lead at half time.
A swift Stade penalty followed by a drop goal from the superb Mathieu Bastareaud shortly after the break handed the French side the initiative.
Quins toiled for a route back into the game and came to within three points, but Rodriguez’s stunning drop goal appeared to be enough to hand the French side victory an end of season fillip.
But that was only the start of the late drama in Cardiff as Quins, abject and unimaginative for long periods of the second half, turned a six-point deficit into a one-point lead in the space of two frenetic minutes.
Marler fed England's Danny Care, who broke clear before aiming a little chip out to the corner to where Camacho was on hand to score.
Evans, who was generally excellent with the boot, had missed a routine kick for touch just minutes before, but the New Zealander held his nerve to kick the winner, capping a stunning finale.