A little caveat: I am not a church history expert (my doctorate is in New Testament), but church history is so important that I have tried to learn as much as possible.
The Sinner's Prayer is, as you suspect, a fairly modern invention. If you walk through the conversion accounts in the book of Acts (or the rest of the NT), you find no reference to such a prayer.
I believe it was Allen Carden's book on Puritanism that pointed out that Puritan preachers sometimes told would-be converts to go out and pray all day to find out from God if they were elect. I'm not sure if most modern evangelicals would consider that a sinner's prayer!
Charles Finney, the famous evangelist of the early 1800s, began using a number of new conversion rituals, including the "Anxious Seat," a pew where seekers would come to repent. Finney described it as a replacement for baptism as a public display of one's initial faith. In the late 1800s, D.L Moody took this practice and moved it to the "Inquiry Room," where the seeker could be counseled and prayed with by Moody's assistants. Apparently, this was the first time that a formal "sinner's prayer" was used. Evangelists of the 20th century used many of Moody's methods, as anyone familiar with Billy Graham's crusades can attest. (Here I am relying on my imperfect memory of a collection of Finney's writings, George Marsden's history of American fundamentalism, and a book on the history of evangelism whose name escapes me - so don't take me as an authority on this).
If we take a walk through all the NT passages on conversion, we find several items emphasized. 1) Repentance - a change of our spiritual orientation away from sin and towards God. 2) Faith - belief that the death and resurrection of Jesus Christ is able to bring forgiveness and right standing with God. 3) Receiving the Holy Spirit / regeneration - Whether perceived by others or not, the new convert receives the Spirit, who enables transformation. 4) Baptism - new believers were always baptized immediately (if possible) in Acts.
The second-century church began delaying baptism for several reasons, both practical and theological. This created a problem that we still deal with today: genuine converts, who have repented, believed in salvation through Christ, and received the Holy Spirit - but have not yet been baptized. We have artificially separated baptism from conversion. Now, an unbaptized convert is nonetheless a real Christian. Many passages in the NT on conversion that omit baptism remind us of that truth, including the story of the bandit on the cross (he was not being crucified for theft, but that's another blog post). But the nature of conversion as an act of turning towards obedience makes one wonder why any new convert would not want to obey the command to get baptized.
One of the reasons that so many genuine converts delay baptism so long, or avoid it altogether, is that other modern rituals - raising a hand, walking down the aisle, the sinner's prayer - have replaced the role of baptism as the primary ritual marking one's passage into fellowship with God.
Is the sinner's prayer a bad thing? It is if we communicate that the prayer itself brings salvation. Salvation comes to the one who repents and believes - and that may happen with or without a formal sinner's prayer. In many cases, emphasis on the sinner's prayer can give the idea that the prayer is a ritual formula that all by itself gives the invoker access to heaven (shades of ex opere operato?). On the other hand, someone who repents and believes will very naturally be ready to pray, so we certainly shouldn't discourage new converts from praying through the ideas involved in conversion!
The picture: from Tomus primus homiliarum super Euangelia... by Johann Eck, 1534.