0

I have a strange behaviour using PHP PDO for a INSERT from a SELECT query. Testing the query directly in MySQL it works well, I get my row inserted :

INSERT INTO sessionid (enc_id, enc_pass, enc_date) 
SELECT AES_ENCRYPT(username, 'aeskey'), AES_ENCRYPT(pwd, 'aeskey'), 
DATE_ADD(NOW(), INTERVAL 15 SECOND) FROM users WHERE username = 'a_user_name';

But using PDO, I have one row per user inserted at once (279 rows) .... Here is the PHP :

$sql_enc = '
    INSERT INTO sessionid (enc_id, enc_pass, enc_date) 
        (SELECT AES_ENCRYPT(username, :aeskey), AES_ENCRYPT(pwd, :aeskey), DATE_ADD(NOW(), INTERVAL 15 SECOND) FROM users WHERE username = :username)
';
$res_enc = $pdo->prepare($sql_enc);
$res_enc->bindParam(':aeskey', $aeskey);
$res_enc->bindParam(':username', $username);
$res_enc->bindParam(':pwd', $username);
$res_enc->execute();
$res_enc = null;

What am I missing? I'm almost sure it's nothing but can't make it insert that single row.

Thank you.

fabien.

feub
  • 547
  • 2
  • 10
  • 22
  • Wait... I spotted the stupid error that I even pasted here : bindParam. $username is assigned to both :username and :pwd. Sorry for the question, and thank you Pekka for the reply. – feub Jun 21 '11 at 09:55
  • ah, fair enough - I misread the query. – Pekka Jun 21 '11 at 09:57

2 Answers2

1

Not that it is the probable problem, but you put a username in the password field in your code. In your query you insert the aeskey there. It is the only difference I can spot.

hoppa
  • 3,011
  • 18
  • 21
  • Yes hoppa, it was this, don't understand why i couldn't spot this before posting on SO.. Sorry guys and thank you :> – feub Jun 21 '11 at 10:11
0

Look at the PHP Documentation for PDO::bindParam. One user there suggested:

...you must use each parameter once and only once...

So, you used username and aeskey twice. Do it this way:

$sql_enc = '
    INSERT INTO sessionid (enc_id, enc_pass, enc_date) 
        (SELECT AES_ENCRYPT(:username1, :aeskey1), AES_ENCRYPT(:pwd, :aeskey2), DATE_ADD(NOW(), INTERVAL 15 SECOND) FROM users WHERE username = :username2)
';
$res_enc = $pdo->prepare($sql_enc);
$res_enc->bindParam(':aeskey1', $aeskey);
$res_enc->bindParam(':aeskey2', $aeskey);
$res_enc->bindParam(':username1', $username);
$res_enc->bindParam(':username2', $username);
$res_enc->bindParam(':pwd', $username);
$res_enc->execute();
$res_enc = null;
Sascha Galley
  • 15,711
  • 5
  • 37
  • 51
  • Shouldn't we be asking why he's selecting from `users` at all when he's providing all of the parameters? :) – Jeff Parker Jun 21 '11 at 09:57
  • Jeff, the why of the query was not my problem, but if you want an answer, I just need to encrypt the user's id and password in a temporary table to pass them to a Java application (not developed by me). About this, I'm open to any suggestion about how to pass parameters between a PHP and a java application as long as the solution is simple and doesn't require any other programs (other than Apache/PHP/MySQL). – feub Jun 21 '11 at 10:10