It depends on the context.
1. It could mean "a very bad thing"
A: I'm going to go visit your mother in law. Would you like to join me?
B: God no! I'd rather stick a pin in my eye! I hate that woman.
2. it could refer to a schoolyard rhyme meaning: "yes, I absolutely promise to do that, and if I don't do it, the universe will do something bad to me."
A: "Do you absolutely promise to come to my dance recital?"
B: "Yes, absolutely. Cross my heart, hope to die, stick a needle in my eye."
3. It could mean "I'm completely surprised. This is the opposite of what I expected."
A: "Did you hear that Sam won the race?"
B: "Well, stick a pin in my eye! I was sure he would lose."
4. it could talk about an actual physical sensation.
Doctor: "How can I help you?"
Patient: "My eyes really, really hurt. It is like someone is sticking pins in them."