No, it's not painful, because you'll be given a local anaesthetic, so you won't feel what they're doing, and it doesn't take very long at all to do.
Please bear in mind that they want to do this in order to keep you safe and well, and to prevent these abnormalities in your cervical cells developing into cancer. I know it's frightening because it's the unknown and any invasive procedure is a horrible thought, but actually, you have been given an opportunity to prevent cancer developing in your body. Trust me, that's an opportunity worth grabbing!! Do you have a partner or a friend who could go with you and hold your hand - I think that would really help you. Also, when you get there, tell the consultant that you're anxious and scared - they will be able to help you feel more comfortable and will talk you through what they're going to do.
You're right that sometimes people do get a urinary tract infection after this procedure. However, it's more likely that you won't get an infection and even if you do, that's really not anything to worry about - it can be very easily treated with standard anti-biotics which will start to clear it up within a few days.
Maria, when you went for your smear test, you were doing something really important to look after yourself, and it's fantastic that you did that. As it's turned out, it was essential that you did that, because there was something there that needed attention. In allowing yourself to have this treatment, you'll be continuing in doing that really important thing of looking after yourself. You deserve to be well looked after both by yourself and by others, and you can definitely go through with this - you definitely have it in you to cope with this, and to know that this is what must be done in order to keep you safe and healthy.
It will be ok - you can do it.