I have seen two syntaxes when calling stored procedures that receive parameters in Laravel, both work perfectly:
//Concatenando parametro
DB::select('exec Miprocedimiento "'.$parametro.'"');
//Utilizando ?
DB::select('exec Miprocedimiento ?', array($parametro));
What is the correct way? Is there any difference in terms of performance, security?
Performance-wise, both options are equivalent.
But not in terms of security.
In general, when writing commands to be interpreted by the database engine, precautions should be taken to prevent attacks by users of an application, for example SQL injection .
For this, it is preferable to use, whenever possible, parameters, and not concatenate texts, less texts entered by the user, as part of an SQL statement (not only to execute stored procedures, but in general).
For this reason, the second form that you include in your question is more recommendable than the first.
Well, the text that the engine will interpret will be only
'exec Miprocedimiento ?'
, being explicitly defined that the value of$parametro
must then be passed in full when invokingMiprocedimiento
.Instead, with the first option:
There is a risk, for example, if the user manages to manipulate the variable
$parametro
to have the text:The text that the engine will receive, which is the product of the concatenation, will be:
There is a risk that this ends up destroying information in the database.
There are routines to sanitize the text entered by the user, but that will never be as effective as using parameters.
In case you want to do it this way:
You could use the double quotes like this
To not concatenate ... although it is not recommended to do it this way because you are vulnerable to sql injection attacks ... the correct thing is
but if your procedure has more than 2 parameters, this is recommended
It is more recommended than the first.