PHP Programming - mcrypt blob upload problem to MySQL

Author

mcrypt blob upload problem to MySQL

Sophisticado

2006-08-30, 6:57 pm

Greetings

I have a script in which I am collecting sensitive information via a
form (METHOD=POST) and encrypting the posted variable (format = BLOB)
using mcrypt, then saving it in a MySql table. Using my test
script,everything works fine. Using my production scrypt, everything
works fine for data posted with fewer than 8 characters. If I try to
upload data longer than 8 characters, I get this error message:

You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'ióU¹
”šC!ÊŒB', '01', '2004', NULL, '150')' at line 1

The characters ióU¹”šC!ÊŒB' after "near" are the encrypted characters.

There does not seem to be any difference between the test and production
scrypts.

Here is the syntax I am using for saving the record:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "myTable"))
{
$insertSQL = sprintf("INSERT INTO myTable (`Date`, LastName,
FirstName, EcryptedBlob) VALUES (%s, %s, %s, %s)",
GetSQLValueString($_POST['Date'], "text"),
GetSQLValueString($_POST['Lastname'], "text"),
GetSQLValueString($_POST['Firstname'], "text"),
GetSQLValueString($encrypted,"text"));

php v. 5.0.5
MySql v. 4.1.9

Any help would be appreciated.

Andy Hassall

2006-08-30, 6:57 pm

On Wed, 30 Aug 2006 11:21:47 -0500, Sophisticado <Sophsiticado> wrote:

>I have a script in which I am collecting sensitive information via a
>form (METHOD=POST) and encrypting the posted variable (format = BLOB)
>using mcrypt, then saving it in a MySql table. Using my test
>script,everything works fine. Using my production scrypt, everything
>works fine for data posted with fewer than 8 characters. If I try to
>upload data longer than 8 characters, I get this error message:
>
>You have an error in your SQL syntax; check the manual that corresponds
>to your MySQL server version for the right syntax to use near 'ióU¹
>?¨C!Ê¼B', '01', '2004', NULL, '150')' at line 1
>
>The characters ióU¹?¨C!Ê¼B' after "near" are the encrypted characters.
>
>There does not seem to be any difference between the test and production
>scrypts.
>
>Here is the syntax I am using for saving the record:
>
>if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "myTable"))
>{
> $insertSQL = sprintf("INSERT INTO myTable (`Date`, LastName,
>FirstName, EcryptedBlob) VALUES (%s, %s, %s, %s)",
> GetSQLValueString($_POST['Date'], "text"),
> GetSQLValueString($_POST['Lastname'], "text"),
> GetSQLValueString($_POST['Firstname'], "text"),
> GetSQLValueString($encrypted,"text"));
>
>php v. 5.0.5
>MySql v. 4.1.9

Where is "GetSQLValueString" defined?

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Sophisticado

2006-08-30, 6:57 pm

Andy Hassall <andy@andyh.co.uk> wrote in
news:4lnbf2hc4akvqm2955c6rb1mlsu1kbp1s4@
4ax.com:

> On Wed, 30 Aug 2006 11:21:47 -0500, Sophisticado <Sophsiticado> wrote:
>
?¨C!Ê¼B', '01', '2004', NULL, '150')' at line 1[color=darkred]
>
> Where is "GetSQLValueString" defined?
>

Here is the function before the encryption at the top of the script:

function GetSQLValueString($theValue, $theType, $theDefinedValue = "",
$theNotDefinedValue = "") {
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) :
$theValue;

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" :
"NULL"; break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue :
$theNotDefinedValue; break;
}
return $theValue;
}

Jerry Stuckle

2006-08-31, 7:57 am

Sophisticado wrote:
> Andy Hassall <andy@andyh.co.uk> wrote in
> news:4lnbf2hc4akvqm2955c6rb1mlsu1kbp1s4@
4ax.com:
>
>
>
> ?¨C!Ê¼B', '01', '2004', NULL, '150')' at line 1
>
>
>
>
>
> Here is the function before the encryption at the top of the script:
>
> function GetSQLValueString($theValue, $theType, $theDefinedValue = "",
> $theNotDefinedValue = "") {
> $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) :
> $theValue;
>
> switch ($theType) {
> case "text":
> $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
> break;
> case "long":
> case "int":
> $theValue = ($theValue != "") ? intval($theValue) : "NULL";
> break;
> case "double":
> $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" :
> "NULL"; break;
> case "date":
> $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
> break;
> case "defined":
> $theValue = ($theValue != "") ? $theDefinedValue :
> $theNotDefinedValue; break;
> }
> return $theValue;
> }

Well, among other things, you should be using mysql_real_escape_string()
on all text values before you insert/update the database.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Sponsored Links