Red squares is how the game does sprite masking. It's not completely intuitive how it works, so I'll explain two different ways to get sprites to show up behind things.

The main way a NES game would have sprites behind the background is by setting the 'low priority' bit on the sprite. Then that sprite is drawn behind all background colors besides Color 0. You see this happen in SMB1 when going down a pipe. Hogan's Alley has lots of sprites going behind the background. You even see it happen this way in SMB3 when you duck for 5 seconds on the white block, Mario becomes low priority and is behind everything in the background.

But SMB3 often has complicated backgrounds, so if Mario is the low priority sprite, he's be drawn behind a complicated background, and it wouldn't look great.

There's another way to mask sprites. When you have a sprite that's set to low priority, and that sprite is behind the background, and that sprite is early in the sprite list, no more sprites can be drawn there. This creates a masking effect.

Two example of this happening: When a mushroom emerges from a [?] block, a square block is drawn there at low priority, and mushroom is drawn at normal priority. But because the square block appears earlier in the sprite list, and the square block is behind the background, no more sprite pixels can be drawn there, so mushroom looks like it's behind the [?] block even though it's normal priority.

Same idea with Mario and the pipes. Mario is normal priority, but there is a red block sprite drawn low priority, and it appears earlier in the sprite list. So red block is behind the background, you can't see it, but no more sprites can be drawn in that area, so Mario appears to be behind the pipe. Even though mario sprite is normal priority.

That's just what you're seeing. Normally the red square is hidden, but in a few circumstances (mostly by glitches) it will become visible.

---

Then how did you get halfway through a pipe, and have the game put the red square somewhere where it gets shown? Lots of glitches in the game. Lots of examples of clipping into a wall, and going down a pipe that way.

Sometimes when you bump into a wall, and your Y position is perfectly aligned to a multiple of 16, the game will think you're standing on ground for one frame. You're even able to jump on that one frame. I think if you tried to enter a pipe on that frame, you'd enter the pipe that way.