passing string in a query to MySQL database in MATLAB

I am using MySQL with MATL开发者_StackOverflow中文版AB, and I want to get a name from user, and pass it to the table in mySQL, but it is rejecting a variable name in place of string

var_name=input('enter the name:');

mysql('insert into table (name) values (var_name)');

Any suggestions?

---

FIRST read the comments to this question - you don't want to shoot yourself in the foot with a mysql injection security problem. You have been warned. Now, to solve your current problem, without addressing the security risk of the whole approach when it comes to building SQL queries, read on...

In principle Amro has already posted two solutions for you which work, but since you have not accepted it I'll explain further.

Your problem is that you are not telling MATLAB which parts of your query it should interpret as a literal string, and which parts it should interpret as a variable name. To solve this, you can just end the literal string where appropriate, i.e. after the opening brackets, and then start them again before the closing brackets.

In between those literal strings you want to add the contents of your variables, so you need to tell MATLAB to concat your literal strings with your variables, since the mysql command probably expects the whole query as a single string. So in essence you want to take the string 'insert into table(' and the string saved in the variable name and the string ') values (' and so on and glue them into one big string. Amro and Isaac have shown you two solutions of how to do this without much explanation:

horzcat('insert into table (', name, ') values (', var_name, ')')

uses the function horzcat, while

['insert into table (' name ') values (' var_name ')']

uses the fact that MATLAB treats strings as arrays of characters so that you can just use square brackets to form a large array containing the strings one after the other.

The third solution, offered by Amro, is a bit more sublte:

sprintf('insert into table (%s) values (%s)',name,var_name)

It tells the function sprintf (which is made for that purpose) "take the string which I supply as first parameter and replace occurences of %s with the strings I supply as the following parameters. This last technique is in particular useful if you also need to insert numbers into your string, because sprintf can also convert numbers to string and allows fine control over how they are formatted. You should have a close look at the help page for sprintf to know more :-).

---

Try this instead:

mysql(['insert into table (' name ') values (' var_name ')']);

or even:

mysql(sprintf('insert into table (%s) values (%s)',name,var_name));

---

I believe the problem you are having is the same as the one in this other question. It sounds like you want to create a command string that itself contains a ' delimited string, which would require you to escape each ' with another ' when you create your command string (note the first example in this string handling documentation). Note also you may want to use the 's' option for the INPUT function:

var_name = input('Enter the name: ','s');  %# Treats input like a string
commandString = sprintf('insert into table (name) values (''%s'')', var_name);
                            %# Note the two apostrophes --^
mysql(commandString);

If I were to enter Ken for the input, the string commandString would contain the following:

insert into table (name) values ('Ken')

And of course, as others have already mentioned, beware injection vulnerabilities.