Ygthkb

In a post apocalypse world, with no power and few survivors, would Satnav still work?

How to Build a List from Separate Lists

How can changes in personality/values of a person who turned into a vampire be explained?

Sed-Grep-Awk operations

What could cause an entire planet of humans to become aphasic?

What is an efficient way to digitize a family photo collection?

Expression for "unconsciously using words (or accents) used by a person you often talk with or listen to"?

Is there a configuration of the 8-puzzle where locking a tile makes it harder?

Minimum Viable Product for RTS game?

Is the tritone (A4 / d5) still banned in Roman Catholic music?

Algebraic proof that two statements of the fundamental theorem of algebra are equivalent

Is the UK legally prevented from having another referendum on Brexit?

EM Vs PM Speaker

Taking an academic pseudonym?

How can I handle players killing my NPC outside of combat?

How do I fight with Heavy Armor as a Wizard with Tenser's Transformation?

Converting numbers to words - Python

Two oatmeal pies a day keep the doctor away?

Can an attached stirge deal no more than 10 HP of damage before it detaches?

What does @ mean in a hostname in DNS configuration?

What is formjacking?

Can a Way of Shadow Monk use Shadow Step to teleport to a dark ceiling and then body slam another creature?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Short story about a man betting a group he could tell a story, and one of them would disappear and the others would not notice

Dynamic my sql error

MySQL #1064 error on line 2prepare stmt is not workingReturning true or false in a custom functionDELIMITER issue in MySQL 5.6How to Solve ERROR 1064 (42000)?Stored Procedure ErrorDatabase Trees with MySqlMySQL stored procedure returns nullWhy am I getting a syntax error when trying to create a stored procedure in MySQL?Creating MySQL Trigger

0

I am below error when i try to execute procedure.

delimiter $$

USE `Test`$$

DROP PROCEDURE IF EXISTS `DYNAMIC_MYSQL`$$

CREATE PROCEDURE DYNAMIC_MYSQL()

BEGIN

DECLARE DONE INT;

DECLARE CHECKNUMBER VARCHAR(50);

DECLARE QUERY1 VARCHAR(100);

DECLARE stmt VARCHAR(100);

SET NUMBER='3131';



SET QUERY1=CONCAT('SELECT number FROM abc WHERE number LIKE "%',NUMBER,'%"');

PREPARE stmt FROM @QUERY1;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SELECT QUERY1;



END;

Error:CALL Test.DYNAMIC_MYSQL() Error Code: 1064. 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 'NULL' at line 1 0.000 sec

Please share your suggestions on this.

mysql

share|improve this question

edited Sep 22 '14 at 15:52

Taryn♦

7,04932961

asked Sep 22 '14 at 15:49

VenkatVenkat

12

bumped to the homepage by Community♦ 10 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

0

I am below error when i try to execute procedure.

delimiter $$

USE `Test`$$

DROP PROCEDURE IF EXISTS `DYNAMIC_MYSQL`$$

CREATE PROCEDURE DYNAMIC_MYSQL()

BEGIN

DECLARE DONE INT;

DECLARE CHECKNUMBER VARCHAR(50);

DECLARE QUERY1 VARCHAR(100);

DECLARE stmt VARCHAR(100);

SET NUMBER='3131';



SET QUERY1=CONCAT('SELECT number FROM abc WHERE number LIKE "%',NUMBER,'%"');

PREPARE stmt FROM @QUERY1;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SELECT QUERY1;



END;

Error:CALL Test.DYNAMIC_MYSQL() Error Code: 1064. 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 'NULL' at line 1 0.000 sec

Please share your suggestions on this.

mysql

share|improve this question

edited Sep 22 '14 at 15:52

Taryn♦

7,04932961

asked Sep 22 '14 at 15:49

VenkatVenkat

12

bumped to the homepage by Community♦ 10 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

I am below error when i try to execute procedure.

delimiter $$

USE `Test`$$

DROP PROCEDURE IF EXISTS `DYNAMIC_MYSQL`$$

CREATE PROCEDURE DYNAMIC_MYSQL()

BEGIN

DECLARE DONE INT;

DECLARE CHECKNUMBER VARCHAR(50);

DECLARE QUERY1 VARCHAR(100);

DECLARE stmt VARCHAR(100);

SET NUMBER='3131';



SET QUERY1=CONCAT('SELECT number FROM abc WHERE number LIKE "%',NUMBER,'%"');

PREPARE stmt FROM @QUERY1;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SELECT QUERY1;



END;

Error:CALL Test.DYNAMIC_MYSQL() Error Code: 1064. 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 'NULL' at line 1 0.000 sec

Please share your suggestions on this.

mysql

share|improve this question

edited Sep 22 '14 at 15:52

Taryn♦

7,04932961

asked Sep 22 '14 at 15:49

VenkatVenkat

12

delimiter $$

USE `Test`$$

DROP PROCEDURE IF EXISTS `DYNAMIC_MYSQL`$$

CREATE PROCEDURE DYNAMIC_MYSQL()

BEGIN

DECLARE DONE INT;

DECLARE CHECKNUMBER VARCHAR(50);

DECLARE QUERY1 VARCHAR(100);

DECLARE stmt VARCHAR(100);

SET NUMBER='3131';



SET QUERY1=CONCAT('SELECT number FROM abc WHERE number LIKE "%',NUMBER,'%"');

PREPARE stmt FROM @QUERY1;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SELECT QUERY1;



END;

share|improve this question

edited Sep 22 '14 at 15:52

Taryn♦

7,04932961

asked Sep 22 '14 at 15:49

VenkatVenkat

12

share|improve this question

edited Sep 22 '14 at 15:52

Taryn♦

7,04932961

asked Sep 22 '14 at 15:49

VenkatVenkat

12

edited Sep 22 '14 at 15:52

Taryn♦

7,04932961

edited Sep 22 '14 at 15:52

Taryn♦

7,04932961

asked Sep 22 '14 at 15:49

VenkatVenkat

12

asked Sep 22 '14 at 15:49

VenkatVenkat

12

1 Answer
1

active

oldest

votes

0

You're using two different kinds of variables.

QUERY1 is a stored program variable, with program scope, while @QUERY1 is a user-defined variable, with session scope.

Prepared statements also have session scope, so they only work with user-defined variables... but your procedure never initializes @QUERY1, so it's NULL. Hence, the error message.

You can remove the DECLARE QUERY1 statement and change the SET statement to SET @QUERY1 = .. and your code should work.

However, it's not a good practice to concatenate strings together. You need to use placeholders.

SET @NUMBER='3131'; 

SET @QUERY1='SELECT number FROM abc WHERE number LIKE CONCAT("%",?,"%")';

PREPARE stmt FROM @QUERY1; EXECUTE stmt USING @NUMBER;

Here, the value in the variable @NUMBER will be used in place of the ?, but not by simple string manipulation. It's impossible, when building a query this way for the server to be manipulated into getting the SQL and the data confused, and opening up a SQL injection vulnerability.

As originally written, if NUMBER was ever changed to use a string that was passed in to the procedure from outside, you would have code that's potentially easily manipulated into doing something you don't want.

Also, never declare program variables with the same names as columns. That creates an ambiguous situation, in which WHERE number... could be interpreted as referring to the variable, not the column, which is also not what you want. MySQL does not warn you when you do this, but unexpected results are guaranteed.

share|improve this answer

answered Sep 23 '14 at 2:16

Michael - sqlbotMichael - sqlbot

19.2k23261

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f77294%2fdynamic-my-sql-error%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

You're using two different kinds of variables.

QUERY1 is a stored program variable, with program scope, while @QUERY1 is a user-defined variable, with session scope.

Prepared statements also have session scope, so they only work with user-defined variables... but your procedure never initializes @QUERY1, so it's NULL. Hence, the error message.

You can remove the DECLARE QUERY1 statement and change the SET statement to SET @QUERY1 = .. and your code should work.

However, it's not a good practice to concatenate strings together. You need to use placeholders.

SET @NUMBER='3131'; 

SET @QUERY1='SELECT number FROM abc WHERE number LIKE CONCAT("%",?,"%")';

PREPARE stmt FROM @QUERY1; EXECUTE stmt USING @NUMBER;

Here, the value in the variable @NUMBER will be used in place of the ?, but not by simple string manipulation. It's impossible, when building a query this way for the server to be manipulated into getting the SQL and the data confused, and opening up a SQL injection vulnerability.

As originally written, if NUMBER was ever changed to use a string that was passed in to the procedure from outside, you would have code that's potentially easily manipulated into doing something you don't want.

Also, never declare program variables with the same names as columns. That creates an ambiguous situation, in which WHERE number... could be interpreted as referring to the variable, not the column, which is also not what you want. MySQL does not warn you when you do this, but unexpected results are guaranteed.

share|improve this answer

answered Sep 23 '14 at 2:16

Michael - sqlbotMichael - sqlbot

19.2k23261

add a comment | 

0

You're using two different kinds of variables.

QUERY1 is a stored program variable, with program scope, while @QUERY1 is a user-defined variable, with session scope.

Prepared statements also have session scope, so they only work with user-defined variables... but your procedure never initializes @QUERY1, so it's NULL. Hence, the error message.

You can remove the DECLARE QUERY1 statement and change the SET statement to SET @QUERY1 = .. and your code should work.

However, it's not a good practice to concatenate strings together. You need to use placeholders.

SET @NUMBER='3131'; 

SET @QUERY1='SELECT number FROM abc WHERE number LIKE CONCAT("%",?,"%")';

PREPARE stmt FROM @QUERY1; EXECUTE stmt USING @NUMBER;

Here, the value in the variable @NUMBER will be used in place of the ?, but not by simple string manipulation. It's impossible, when building a query this way for the server to be manipulated into getting the SQL and the data confused, and opening up a SQL injection vulnerability.

As originally written, if NUMBER was ever changed to use a string that was passed in to the procedure from outside, you would have code that's potentially easily manipulated into doing something you don't want.

Also, never declare program variables with the same names as columns. That creates an ambiguous situation, in which WHERE number... could be interpreted as referring to the variable, not the column, which is also not what you want. MySQL does not warn you when you do this, but unexpected results are guaranteed.

share|improve this answer

answered Sep 23 '14 at 2:16

Michael - sqlbotMichael - sqlbot

19.2k23261

add a comment | 

You're using two different kinds of variables.

QUERY1 is a stored program variable, with program scope, while @QUERY1 is a user-defined variable, with session scope.

Prepared statements also have session scope, so they only work with user-defined variables... but your procedure never initializes @QUERY1, so it's NULL. Hence, the error message.

You can remove the DECLARE QUERY1 statement and change the SET statement to SET @QUERY1 = .. and your code should work.

However, it's not a good practice to concatenate strings together. You need to use placeholders.

SET @NUMBER='3131'; 

SET @QUERY1='SELECT number FROM abc WHERE number LIKE CONCAT("%",?,"%")';

PREPARE stmt FROM @QUERY1; EXECUTE stmt USING @NUMBER;

Here, the value in the variable @NUMBER will be used in place of the ?, but not by simple string manipulation. It's impossible, when building a query this way for the server to be manipulated into getting the SQL and the data confused, and opening up a SQL injection vulnerability.

As originally written, if NUMBER was ever changed to use a string that was passed in to the procedure from outside, you would have code that's potentially easily manipulated into doing something you don't want.

Also, never declare program variables with the same names as columns. That creates an ambiguous situation, in which WHERE number... could be interpreted as referring to the variable, not the column, which is also not what you want. MySQL does not warn you when you do this, but unexpected results are guaranteed.

share|improve this answer

answered Sep 23 '14 at 2:16

Michael - sqlbotMichael - sqlbot

19.2k23261

SET @NUMBER='3131'; 

SET @QUERY1='SELECT number FROM abc WHERE number LIKE CONCAT("%",?,"%")';

PREPARE stmt FROM @QUERY1; EXECUTE stmt USING @NUMBER;

share|improve this answer

answered Sep 23 '14 at 2:16

Michael - sqlbotMichael - sqlbot

19.2k23261

answered Sep 23 '14 at 2:16

Michael - sqlbotMichael - sqlbot

19.2k23261

answered Sep 23 '14 at 2:16

Michael - sqlbotMichael - sqlbot

19.2k23261