Base64 as method of sanitizing user input for Mysql

esapi.encoder().encodeforsql example
base64 encode
php sanitize input for mysql
escaping all user supplied input

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'");

The Hitchhiker's Guide to SQL Injection prevention, For Mysql it would be: great site of Stack Overflow, suggesting to escape identifiers the same way as strings. No matter if it's from user input or just mysteriously appeared out of nowhere amidst the� 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 a

If you don't want to use a prepared statement, as simple method is to use real_escape_string

This a standard method for protecting mysql from user input. There's no need to reinvent the wheel.

Don't try to sanitize input – escape output, All this leads to is zillions of user accounts that are used only once. Since 2012 you can't even use the mysql extension without getting a Sanitizing your input means changing them permanently. from_base64 (input) The input data type should be any string data type (if input data type is not a string data type, then automatic to-string conversion happens first). The output data type is BLOB. If the input is NULL, then the output is NULL. If the input is a badly formed base64 string, then the output is NULL.

No, don't use base64 encoding as a way to close SQL Injection vulnerabilities. Firstly, SQL Injection is not a problem that needs to be solved; it's been solved already.

It was already solved in the earliest releases of commercial relational databases (e.g. DB2, Oracle, Sybase) All of those provided interfaces that supported prepared statements using placeholders for bind values.

And MySQL has provided the "real escape string" function since aleast since at least version 3.x, fifteen years ago (back in 2000.)

The real problem is developers ignoring SQL injection vulnerabilities in the code they write. When these vulnerabilities are pointed out, the most common response seems to be "Oh, this code is just for learning. I don't need to be concerned with that right now." And they learn by crafting code that is vulnerable, and they adopt these bad coding practices. They share their newly gained know-how, and examples of bad coding practices proliferate, and new programmers learn from those bad examples, and adopt the same bad coding practices.

The best practice to avoid SQL Injection vulnerabilities is to use prepared statements with bind values. That does EXACTLY what you propose in your question, separating the static SQL text which is known to be "safe", from the potentially "unsafe" values supplied. That's the right way to write the code.

(This discussion doesn't address the issue of inserting potentially unsafe values into the database, and how bad code retrieves those values and handles them as if they were "safe", rather than handling them as if they were potentially "unsafe".)

You'd be quite right to point out that earlier versions of MySQL didn't support real "server side" prepared statements; the client library emulated support for prepared statements. The client library accepted the prepared statements and the bind values, and bundled those together into SQL text it sent to the server, but at least the framework was there in the interface. The MySQL client library took care of "escaping" the bind values to make them safe for inclusion in the SQL text, but that responsibility was handled by the client library, not by the caller.)

Some MySQL interfaces (in particular, the deprecated and ever popular PHP mysql interface) does not provide support for prepared statements and bind variables. But it does provide the next best thing, access to the mysql_real_escape_string function. The documentation explains that this function should be used, but you wouldn't know it's use was at all important, based on the overwhelming volume of code examples that don't use it.

If you have to use the PHP mysql_ interface, then do it right, use the mysql_real_escape_string function BEFORE including potentially unsafe strings within SQL text.

$sqltext = "SELECT * FROM Users WHERE username= '" . mysql_real_escape_string($userinput) . "'"; 
mysql_query($sqltext);

If you don't have to use the deprecated mysql interface, then use mysqli or PDO. Both of those interface support prepared statements and bind variables. (Not that you would ever know that these actually provide support for these features, based on the sheer volume of code examples that entirely ignore these features, and blithely continue the same bad coding practices that plague the examples of mysql interface. (Some developers seem to think that mysqli and PDO "fix" SQL injection vulnerabilities, without understanding that its the code that they write that has the SQL Injection vulnerabilities, not the interface they use.

It would be unfair to single out PHP and MySQL as the sole source of SQL injection vulnerabilities. These same vulnerabilities are prevalent in numerous examples of vulnerable code, written using Microsoft Visual Basic, Visual C++, C#, et al.


Bottom line, base64 encoding is NOT the solution to SQL Injection vulnerabilities. (There may be some good reasons to use base64 encoding, but decidedly, avoiding SQL Injection is NOT one of those reasons.)

base64_encode - Manual, base64_encode — Кодирует данные в формат MIME base64 User Contributed Notes 33 notes dot net's ( http://php.net/manual/en/function.base64 -encode.php#103849 ) You may wish to rtrim (or escape) trailing ='s for use in a URI. > However, as base64_encode and decode are fully deterministic and the functions are > fully standardized (i.e. all versions of base64_encode and decode around should produce > the same output for a given input), I think people will be willing to drop their own > implementations of base64 if it would be included natively.

Yes, it will do the job

Technically, you could do this:

    $name = "Joe Smith";
    $b64_name = base64_encode($name);
    $sql = "select * from users where username=from_base64('$b64_name');";

...and it would work. Quite to the point, actually: you prevented the string to break out of its place. You convert it, and mysql converts it back. It's a minimalistic way to protect stuff without changing the whole DB access infrastructure. Oldschool? Sure. Comes in handy? Well, yes, sometimes it does. I work a lot with legacy code and in certain situations this method saves a lot of plumbing.

But you have better options

We're not in the old days anymore. PDO can bind values for real, it's fast, it makes your code cleaner, it's convenient and easy to get used to. (Just remember to always turn off ATTR_EMULATE_PREPARES. Silly how it's not the default.)

Concatenating queries was never really the way, we just did it because there was nothing else around. So just go with PDO and keep this dirty trick for cases where you have nothing else in reach.

Encoder (ESAPI 2.0 2.0_rc11 API), Validator implementations should call canonicalize on user input before validating to Escaping; MySQL Escaping; Oracle Escaping; Percent Encoding ( aka URL Encoding) Encode for Base64. This article mainly introduces the implementation of file and Base64 string conversion based on Java. The example code is introduced in detail in this article, which has certain reference learning value for your study or work. You can refer to the following for your friends In the project, we encountered the need to convert the …

SQL Injection Bypassing WAF Software Attack, consists of insertion or “injection” of a SQL query via the input data from the client Bypassing WAF: SQL Injection - Normalization Method Example Number (1) of 'user' OR mid(password,1,1)=0x2a select user from mysql.user where user� User experience can be greatly improved on an image upload feature if we enable the user to preview the image they have selected before actually uploading it to the server by clicking the upload button. In this tutorial, we will create a form that takes two inputs: the user's profile picture (image), and their bio (text).

How to validate and sanitize user input with PHP , The above SQL command is error-free, and thus the MySQL server will execute it. But, what if the The filter_var function is used for such a purpose. This will sanitize the input string, and block any HTML tag from entering into the database. Java provides a class Base64 to deal with encryption. You can encrypt and decrypt your data by using provided methods. You need to import java.util.Base64 in your source file to use its methods. This class provides three different encoders and decoders to encrypt information at each level. You can use these methods at the following levels.

The Web Application Hacker's Handbook: Discovering and , logic, 317–318 code review black-box testing, 578–579 methodology, 579– 580 148–149 user impersonation functionality, 149–151 usernames non- unique, 307 out-of-band channels, 274–275 MS-SQL, 275 MySQL, 276–277 Oracle,� Send a base64 string throughout postman, when API loaded it will invoke Upload_file() function, which decode the file, extract the mime type dynamically using finfo_buffer extension, also extract extension by mime using mime2ext() function, add a unique name for the file, finally save it into PHP server and MySQL server

Comments
  • No, don't use base64 encoding as a way to close SQL Injection vulnerabilities. It's not a problem that needs to be solved. The earliest commercial relational databases (DB2, Oracle, Sybase) provided prepared statements with bind variables. And MySQL has provided the "escape string" function since way back, version 3.x at least. The real problem is developers ignoring SQL injection vulnerabilities in the code they write "oh, this is just for development", and continue promoting bad coding practices. Best practice is prepared statements with bind variables. Next best is real_escape_string.
  • I agree. Base64 is far from an ideal option. However, escaping data is still a form of "non-invasive" encoding to keep user input from mixing with the actual query. If mysql required query data and user data to be separate by for example supplying them in separate arguments in the query function, or any other isolated variable, this would make the query safer by nature. Yes, the queries would have to be structured very differently to allow for multiple separate arguments in one function, in stead of one large string... I do however agree that if used properly, sql injection is already solved.
  • @DanielValland I disagree, as I've explained in the 3rd paragraph - using base64 changes the information so the database is now filled with junk. If not using placeholders (the best solution), the correct alternative is to use a function specifically designed to encode the values per SQL string literal rules (i.e. mysql_real_escape_string). Such a function prevents SQL injection when used consistently and avoids all of the negative issues associated with the base64 proposal as it does not change the information after the SQL is parsed.
  • @DanielValland While using "SELECT * FROM Users WHERE username = FROM_BASE64('$base64Input')" would avoid the issues associated with storing the base64-encoded data it is an unneeded non-standard approach that complicates the queries and is neither safer nor advantageous compared to using a proper escaping (i.e. mysql_real_escape_string) function which is a standard, albeit legacy, technique for this task. But alas, just use mysqli/PDO and placeholders. Placeholders solve the problem of SQL Injection cleanly and grants the wish of not muddling the query shape and data.