Prevent injection in queries with execute

Question

I'm developing a reporting web with Visual Studio 2017. All my MYSQL queries are parameterized and works properly. The problem is in some queries that have EXECUTE command.

For example:

Instead of

SELECT * FROM tabla WHERE id=@id

I've put

set @sentencia='SELECT * FROM tabla WHERE id=@id'
PREPARE stmt FROM @sentencia;
EXECUTE stmt using @id;
DEALLOCATE PREPARE stmt;

The execute case doesn't break but doesn't work because return no data.

I've tried this:

set @sentencia='SELECT * FROM tabla WHERE id=?'
PREPARE stmt FROM @sentencia;
EXECUTE stmt using @id;
DEALLOCATE PREPARE stmt;

But It doesn't work. The error message is:

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 '16; DEALLOCATE PREPARE stmt' at line 1

16 corresponds to the value I've given to the parameter @id in my code.

       Dim myConnectionMYSQL As New MySql.Data.MySqlClient.MySqlConnection
        myConnectionMYSQL.ConnectionString = CadenaDeConexion()
        myConnectionMYSQL.Open()
        Dim myCommandMYSQL As New MySql.Data.MySqlClient.MySqlCommand(_Sentencia, myConnectionMYSQL)
        myCommandMYSQL.CommandTimeout = 9000
        myCommandMYSQL.Parameters.Add("@id", SqlDbType.Int, 100)
        myCommandMYSQL.Parameters("@id").Value = 16
        Dim daMYSQL As New MySql.Data.MySqlClient.MySqlDataAdapter(myCommandMYSQL)
        Dim dtMYSql As New DataTable
        daMYSQL.Fill(dtMYSql)

Anyone knows a possible solution? Thanks a lot!

Where do you set @id ?

Daniel E.
– Daniel E.

2017-09-05 12:30:00 +00:00
Commented Sep 5, 2017 at 12:30 — Daniel E.
– Daniel E., Commented Sep 5, 2017 at 12:30
Did you forget a ";" at the end of the set?

muffi
– muffi

2017-09-05 12:49:48 +00:00
Commented Sep 5, 2017 at 12:49 — muffi
– muffi, Commented Sep 5, 2017 at 12:49
Sorry I forget write it. In my code has the ; jeje

Pablo pas
– Pablo pas

2017-09-05 13:28:01 +00:00
Commented Sep 5, 2017 at 13:28 — Pablo pas
– Pablo pas, Commented Sep 5, 2017 at 13:28
I set the @id in my .net code. I've changed above

Pablo pas
– Pablo pas

2017-09-05 13:28:49 +00:00
Commented Sep 5, 2017 at 13:28 — Pablo pas
– Pablo pas, Commented Sep 5, 2017 at 13:28

Trevor · Accepted Answer · 2017-09-05 13:22:03Z

1

Give this a shot...

PREPARE stmt FROM 'SELECT * FROM tabla WHERE id=?';     
SET @id= 'YOURID';
EXECUTE stmt USING @id;     
DEALLOCATE PREPARE stmt;

In your example you are not setting the id. It should have been...

 SET @id = 'SOMEID';

answered Sep 5, 2017 at 13:22

Trevor

8,0156 gold badges35 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pablo pas Over a year ago

But my query is dynamic. Depends on the filter that the user choose, the @id is different. If I just replace the text in SET @id= [MYID];, I don't know if it would prevent SQL injection.

Collectives™ on Stack Overflow

Prevent injection in queries with execute

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related