It is not a part of the ANSI SQL standard, hence it is non-standard. So is LIMIT and a bunch of other MySQL proprietary features. The trade-off is, of course, that with LIMIT you gain some functionality at the expense of portability.
With PASSWORD, though, you really gain nothing other than an increased dependancy on MySQL. It provides a proprietary method for one-way hashing of a password.
A standard function, availible in virtually every programming language and library for one-way hashing is MD5. It is mathematically secure and standardized (meaning MD5 in one language is the same as MD5 in another). Further you can even perform MD5 in SQL in case both your RDBMS and programming language didn't support MD5.
Nothing is stopping MySQL from changing the way PASSWORD functions. Perhaps they change the amount of characters the password function outputs. Perhaps they change the hash function so that old hashes no longer match. There's no guarantee that will not happen with MD5, although since it is standardized the liklihood of that happening is far, far less.
Anyway, when you run array_map( 'addslashes', $_POST ); you are securing your SQL against string-based attacks. The issue is not 'crap' as you stated earlier; we have seen how it can be used to elevate user privlidges. Further it 'not working' in certain cases as again you have illustrated is not that there is not a problem, but that you already provided a fix for it, which is all the original author was trying to get you to do. It's like people saying "Buckle your seatbelt so you don't die in an accident" and you getting in an accident and saying "Hey I didn't die, what are you talking about?" when you were ALREADY wearing your seatbelt. People who don't buckle up have a much higher risk of being severely injured or killed in a car accident. To those who wear their seatbelts: "Super, just remember to wear them every time!". Similarly, SQL Injection is very real and can happen if one forgets to sanatize their user-submitted variables. However, if you sanatize them, then you won't have a problem, case closed, life is good, etc. :D
However in your case, there is still the issue of integer-based methods since adding slashes will not preclude a comment character or extra SQL going through. So you would need to individually run the intval function on the $_POST members which are supposed to be numeric ONLY.
Names of tables are very easy to deduce (virtually everyone uses 'user', etc.) and as your example showed if you leave your SQL queries unchecked MySQL will spit out an ugly error, typically with the SQL command echoed. That is the SECOND part to the SQL Injection Prevention -- do NOT allow your programming language to echo errors directly. Perform rudimentary error controls so that malicious users cannot gain knowledge of your data structures.
If you are using parameters, such as your $uid, make sure they do not hint as to what the internal strutures are. When I visit show_user.php?uid=bobross it is fairly obvious that uid maps to something like uid, userid, etc.. Internal variables can and should be descriptive but POST/GET/etc. vars should not betray what your internal structures are.