Danny Lennon's side had lost their last eight games against the Scottish champions without scoring but ended that hoodoo in the most dramatic fashion to seal a meeting with Hearts in the final on March 17.
Debutant Esmael Goncalves gave Saints the lead in the eighth minute but Gary Hooper levelled a minute before the interval.
Buddies goalkeeper Craig Samson saved Charlie Mulgrew's penalty four minutes into the second half and Mulgrew compounded his error in the 83rd minute by handling a Gary Teale cross inside the box, allowing former Celt Paul McGowan to score from the spot before Steven Thompson grabbed a third.
Mulgrew ended a desperate afternoon by scoring with the last kick of the match as St Mirren registered their first victory at the national stadium since the Scottish Cup final in 1987.
Celtic, flying high at the top of the Clydesdale Bank Premier League, will play Juventus in their Champions League last-16 clash next month but once again they came up short at Hampden under Neil Lennon, having lost to Ross County, Kilmarnock and Hearts in the last three years.
Today's defeat also means that, for a second successive season, Celtic's hopes of a domestic treble were shattered.
The Hoops boss made three changes to the side that started in the 4-0 win over Dundee United in midweek, with skipper Scott Brown and defenders Adam Matthews and Kelvin Wilson coming in, as Beram Kayal, Mikael Lustig and Thomas Rogne dropped out.
Goncalves , the 21-year-old Portuguese striker who signed on loan from Rio Ave last week, wasted no time in making his mark as his St Mirren career got off to a dream start.
When Teale picked up the ball wide on the right, a well-worked Saints break looked in danger of petering out. However, he knocked a pass back to Conor Newton and when the midfielder swung a cross into the area, Goncalves stretched to send the ball past Lukasz Zaluska and in off the post.
The surge of adrenaline that ran through the Paisley side was tangible and that was exemplified moments later when midfielder John McGinn tested Zaluska with a left-footed drive from 25 yards.
The Parkhead side shrugged off their initial lethargy and forced St Mirren back.
Brown was set up by Joe Ledley in the 20th minute but his angled drive from eight yards deflected off Saints stopper Marc McAusland for a corner, which came to nothing.
Moments later, Brown's searching pass evaded St Mirren skipper Goodwin and was pulled out of the air expertly by Hooper but his right-footed volley clipped the bar.
In the 28th minute Georgios Samaras climbed at the back post to get on the end of a Matthews cross but his header came off the top of the bar before going over.
St Mirren looked to have steadied themselves but a minute from the break Celtic levelled through Hooper.
Lassad's pass found Brown storming into the penalty area and when his cross flashed across the six-yard box, Hooper, who scored braces in each of his last two games, hit the ball into the roof of the net for his 23rd of the season.
Celtic looked fired up for the start of the second half and they were presented with a chance to go ahead when referee Collum pointed to the spot after the ball struck Goodwin's arm from Lassad's drive.
However, Samson guessed right, diving low to his right to parry Mulgrew's spot-kick with Ledley firing the rebound over the bar.
The Wales international came closer moments later with an effort which landed on top of the crossbar.
Saints were hanging on but restored their lead from the spot in the 63rd minute when Mulgrew raised his arm to block Teale's cross and McGowan stepped up to confidently send Zaluska the wrong way.
Neil Lennon immediately replaced Lassad with Kris Commons but the Celtic fans were stunned again when Thompson put the Buddies further ahead with a wonderful volley from 12 yards from McAusland's cross.
Mulgrew went close with a header but Saints almost made it four in the 81st minute when Zaluska parried Teale's shot but Thompson failed to get on the end of the rebound.
Saints sensed it was to be their day and they fought valiantly all the way to the final whistle, with Mulgrew's late strike a mere footnote to what will go down as an historic day for the Buddies.