CODEWITHSUNDEEP
phpmysqlbase64sql-injection
h2odev
h2odev

Reputation: 664

Base64 encoded string for simple sql injection

Except performance issue, is base64_encode() a "good" practice to prevent SQL injection?

Of course not for all fields (columns) but just for one TEXT field (Example: in a contact form)

We know that characters returned by base64_encode() function are "safe" and not need even escape (correct me if I'm wrong) but I want to know if it's "safe" in every situation (charset encoding, or something similar ).

Simple practical example:

$dbc = new mysqli( DBHOSTNAME, DBUSERNAME, DBPASSWORD );

$name = preg_replace( "#[^A-Za-z0-9@_]#", "", $_POST[ "name" ] );

$phone = preg_replace( "#[^0-9 +]#", "", $_POST[ "phone" ] );

/*
Assuming this function check and sanitize a valid email
*/ 
$email = is_email( $_POST[ "email" ] );

$dbc->query( "INSERT INTO `testdb`.`testtable` 
( `name`, `email`,`phone`,`message` )
VALUES ( '$name', '$email', '$phone', '" . base64_encode( $_POST[ "message" ] ) . "' )" );

In other hand prepared statement:

$dbc = new mysqli( DBHOSTNAME, DBUSERNAME, DBPASSWORD );

$name = preg_replace( "#[^A-Za-z0-9@_]#", "", $_POST[ "name" ] );
$phone = preg_replace( "#[^0-9 +]#", "", $_POST[ "phone" ] );

/*
Assuming this function check and sanitize a valid email
*/ 
$email = is_email( $_POST[ "email" ] );

$stmt = $dbc->prepare( " INSERT INTO `testdb`.`testtable` 
( `name`, `email `phone`, `message` )
VALUES( '$name', '$email', '$phone', ? ) " );

$stmt->bind_param( 's', $_POST[ "message" ] );
$stmt->execute();

We have to deal with every kinds of hacking and injection techniques every day.

Logically PDO or mysqli (with prepared statement) are safe, but in my case the output (message) will converted anyway in base64 (Required by other parts of project) so why not converting before storing (well some extra space on database)

Upvotes: 3

Views: 5607

Answers (2)

aallkoci
aallkoci

Reputation: 71

Due the nature of base64_encode() function (make binary data survive transport through transport layers that are not 8-bit clean) you don't have to escape anything!

Characters returned are [0-9a-zA-Z/]

But I strongly suggest you to use prepared statement (with mysqli or PDO). Are a tiny bit slower but you will not change the sanitizing logic every time you deal with a table structure.

And also, not less important, maybe in the future you need to index the data on you table (Perhaps for search with LIKE or FULLSEARCH).

Your second example is correct (Bind ALL your parameters)

Upvotes: 3

Daniels118
Daniels118

Reputation: 1227

The base64_encode function is not intended to escape strings, i.e. there is no warranty that a specific SQL dialect won't consider any base64-encoded character as a delimiter; you should always rely on dedicated escape functions. However we don't know any SQL dialect that would consider a base64 character as a delimiter, so it would be safe anyway.

Upvotes: 2

Related Questions