mysql base64 like_Base64作为清理Mysql用户输入的方法

I would assume that the only way to guarantee that a user input is mysql safe is to use either a whitelist where you only allow mysql safe input, or by encoding the input into something that will always return mysql safe. Base64 seams to me like one candidate for such an encoding, along with mysql_escape_string... Is there any reason besides user readability that one should not use Base64 for input sanitation? Is it considered bad practice? I also have a sense that all the filtering of user data to ensure proper safety could have been easier to achieve if the problem was handled by mysql rather then relying on the individual programmer to ensure that the data is safe... For example by using individual functions rather than a query based string, so that the command and user input remains separate throughout the call...

Like: mysql_query("SELECT * FROM Users WHERE username=","$userinput");

Alternatively one could use encoding:

$input = encode($_POST['UserInput']);

mysql_query("SELECT * FROM Users WHERE username='$input'");

解决方案

Don't "sanitize" input as a means to prevent SQL Injection(1) - use placeholders (or proper escaping), always. Be consistent. Be safe. The problem is already solved.

This case will be "safe" due to the limited domain of the base64_encode function. However..

Is there any reason besides user readability that one should not use Base64 for input sanitation? Is it considered bad practice?

It is bad practice and storing base64-encoded values (such that the shown query may work2) carries several negative implications as it changes the information stored: it destroys the ordering of values, makes the information not trivially-searchable, requires an additional "encode/decode" step, and even consumes more space - ouch!

Thus, while there may be specific cases to base64-encode data, this approach is not well-suited as a means to mitigate SQL Injection.

.. proper safety could have been easier to achieve if the problem was handled by mysql rather then relying on the individual programmer to ensure that the data is safe..

The problem is due to accessing SQL via a text protocol where the query command/shape and values are intermixed. The use of correct escaping techniques (e.g. mysql_real_escape_string) fixes this by ensuring that the information is escaped so that the SQL text is parsed as intended - however, unlike a base64-encode step it does not actually change the information supplied!

For example by using individual functions rather than a query based string, so that the command and user input remains separate throughout the call...

This is exactly what placeholders provide! Placeholders are the universally correct approach and should be encouraged. Placeholders allow the query and values to be sent to the database separately when supported by the library/database; and are emulated by escaping otherwise. Correct placeholder usage eliminates SQL Injection and the need for user code to intermix values into SQL command text, which can also make queries easier to write and maintain.

To prevent "individual programmers" from writing terrible queries, the solution is to prevent ad-hoc queries from being scattered around in code: collect the data-access operations into a Data Access Layer (DAL) (possibly in conjunction with an ORM) and only expose relevant actions, ensuring proper use of SQL within the DAL. In simpler projects the DAL is also a suitable location to centrally manage the business rules for sanitation1 and other validation logic.

1More accurately:

Sanitize values for business rules; this should prevent "bad information", such as a username that is too short, contains restricted characters, or otherwise fails to meet business requirements.

Use placeholders to prevent SQL Injection. This is strictly related to transfering the data to SQL and has no bearing on the information therein.

2 While MySQL 5.6.1 adds FROM_BASE64, such that the encoding might simply be used in the SQL command text, this still adds an additional explicit decode step and complicates the query when using such an encoding scheme. This base64 approach is simply not necessary, as there are already proven techniques to prevent SQL injection, and was not proposed in the initial question.