Level 102
Try bindValue() instead of bindParam()
https://www.php.net/manual/en/pdostatement.bindvalue.php
Or use the correct syntax in the sql part
$sql = "ALTER USER :username ACCOUNT UNLOCK";
Sep 28, 2022
29
Level 1
oci_bind_by_name(): ORA-01036: illegal variable name/number on PDO bindparam
public function unlockUser(Request $request){ $unlock_name = $request->unlock_name;
$pdo = DB::connection()->getPdo();
$sql = "ALTER USER ? ACCOUNT UNLOCK";
$unlock = $pdo->prepare($sql);
$unlock->bindParam('username', $unlock_name, PDO::PARAM_STR);
$unlock->execute();
return response()->json([
'status'=>'success',
]);
}
how to solve this i get error in oracle saying illegal name/number when im using bindparam for security purposes
Level 1
i tried the bindValue but still its the same error $unlock_name = $request->unlock_name;
$pdo = DB::connection()->getPdo();
$sql = "ALTER USER ? ACCOUNT UNLOCK";
$unlock = $pdo->prepare($sql);
$unlock->bindValue(1, $unlock_name, PDO::PARAM_STR);
$unlock->execute();
return response()->json([
'status'=>'success',
]);
Level 102
@jurietylier And did you try with proper binding instead?
Level 1
i tried that too its still the same
$pdo = DB::connection()->getPdo(); $sql = "ALTER USER :username ACCOUNT UNLOCK"; $unlock = $pdo->prepare($sql); $unlock->bindParam('username', $unlock_name, PDO::PARAM_STR); $unlock->execute();
return response()->json([
'status'=>'success',
]);
Level 102
@jurietylier Exactly the same error each time or slightly different errors?
Level 1
@Sinnbeck its the same error nothing happens
Level 102
@jurietylier Can you try this?
dd($unlock_name);
Level 102
Or
$pdo = DB::connection()->getPdo();
$sql = "ALTER USER :foo ACCOUNT UNLOCK";
$unlock = $pdo->prepare($sql);
$unlock->bindValue('foo', $unlock_name, PDO::PARAM_STR);
$unlock->execute();
return response()->json([
'status'=>'success',
]);
Level 1
@Sinnbeck its the same error
Level 102
@jurietylier And the dd() gives a string value?
It does seem to have been a know issue. Are you running with a very old version of dbal?
Level 102
Are you using a package to use oracle with laravel?
Level 1
@Sinnbeck yes im currently using yajra oci
Level 102
@jurietylier Just reading the source code. One thing I notice is that they always add the optional : and length to -1
$pdo = DB::connection()->getPdo();
$sql = "ALTER USER :foo ACCOUNT UNLOCK";
$unlock = $pdo->prepare($sql);
$unlock->bindParam(':foo', $unlock_name, PDO::PARAM_STR, -1);
$unlock->execute();
return response()->json([
'status'=>'success',
]);
Level 1
@Sinnbeck you mean the old oracle database version?
Level 1
@Sinnbeck im using latest yajra oci
Level 1
@Sinnbeck i tried this code but the error is still the same
Level 102
@jurietylier try using a statement
DB::statement('ALTER USER ? ACCOUNT UNLOCK', [$unlock_name]);
Level 1
@Sinnbeck im trying this too the simple way the value of the lock_name will show in sql but when passing in the oracle it will show this error
"oci_bind_by_name(): ORA-01036: illegal variable name/number (SQL: ALTER USER MC21 ACCOUNT LOCK)"
Level 102
@jurietylier So if you use DB::statement() you get the same error?
Can you show the exact output of dd($unlock_name);
Level 1
@Sinnbeck OCI8 Version 3.2.1 im using this version of OCI
Level 1
@Sinnbeck Its the same error im getting eventhough i set a static value in unlock name
$unlock_name = "Sample";
$pdo = DB::connection()->getPdo();
$sql = "ALTER USER :foo ACCOUNT UNLOCK";
$unlock = $pdo->prepare($sql);
$unlock->bindParam(':foo', $unlock_name, PDO::PARAM_STR, -1);
$unlock->execute();
Level 102
@jurietylier And
So if you use DB::statement() you get the same error?
I mean instead of all that code
Level 1
@Sinnbeck yes all the code have the same error
Level 102
@jurietylier Ok then maybe try reaching out to yajra/laravel-oci8 and ask them if there could be a problem with running statements
I dont see any reason why this shouldnt work, and it is using laravel, not PDO directly.
$unlock_name = 'foo';
DB::statement('ALTER USER ? ACCOUNT UNLOCK', [$unlock_name]);
return;
Level 1
okay thanks @sinnbeck for the feedback
Level 102
@jurietylier Hm one last idea https://github.com/yajra/laravel-oci8/issues/512
DB::executeProcedureWithCursor('ALTER USER ? ACCOUNT UNLOCK', [$unlock_name]);
I also found this old issue from 2015, but I dont know if its relevant https://github.com/yajra/laravel-oci8/issues/110#issuecomment-167699551
If it is then maybe you are forced to do it without binding (if this works be sure to check for sql injections manually!!)
DB::statement('ALTER USER ' . $unlock_name' . ' ACCOUNT UNLOCK');
Level 1
@Sinnbeck This is the error when i use that executeProcedureWithCursos "Error Code : 6550\r\nError Message : ORA-06550: line 1, column 7:\nPLS-00103: Encountered the symbol "ALTER" when expecting one of the following:\n\n ( begin case declare exit for goto if loop mod null pragma\n raise return select update while with \n <<\n continue close current delete fetch lock insert open rollback\n savepoint set sql execute commit forall merge pipe purge\r\nPosition : 6\r\nStatement : begin ALTER USER :p0 ACCOUNT UNLOCK(:0,:cursor); end;\r\nBindings : [PURE,Resource id #521]\r\n"
Level 102
@jurietylier ok and without binding?
Level 73
You need to follow the Oracle way
https://www.php.net/manual/en/function.oci-bind-by-name.php
The first example on that page
<?php
// Create the table with:
// CREATE TABLE mytab (id NUMBER, text VARCHAR2(40));
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
$stid = oci_parse($conn,"INSERT INTO mytab (id, text) VALUES(:id_bv, :text_bv)");
$id = 1;
$text = "Data to insert ";
oci_bind_by_name($stid, ":id_bv", $id);
oci_bind_by_name($stid, ":text_bv", $text);
oci_execute($stid);
// Table now contains: 1, 'Data to insert '
?>
Please or to participate in this conversation.