sql – Stored procedure in laravel with parameter

Question:

I have seen two syntax when calling stored procedures that receive parameters in laravel, they both work perfectly:

//Concatenando parametro    
DB::select('exec Miprocedimiento "'.$parametro.'"');

//Utilizando ?
DB::select('exec Miprocedimiento ?', array($parametro));

What is the correct way? Is there a difference in terms of performance, safety?

Answer:

In terms of performance, both options are equivalent.

But not in terms of security.

In general, when writing commands to be interpreted by the database engine, precautions must be taken to avoid attacks by the 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 recommended than the first.

DB::select('exec Miprocedimiento ?', array($parametro));

Well, the text that the engine will interpret will only be 'exec Miprocedimiento ?' , being explicitly defined that the value of $parametro must then be passed integrally when invoking Miprocedimiento .

Instead, with the first option:

DB::select('exec Miprocedimiento "'.$parametro.'"');

There is a risk, for example, if the user manages to manipulate the variable $parametro so that it has the text:

123'; drop Tabla; select '

The text that the engine will receive, which is the product of the concatenation, will be:

exec Miprocedimiento '123'; drop Tabla; select ''

There is a risk that this ends up destroying information in the database.

There are routines for sanitizing user-entered text, but that will never be as effective as using parameters.

Scroll to Top