Insert full path of images into MySQL using c#

Question

I need to store the full path of images into MySQL database from c# form and write the following code to achieve this:

string correctFileName3 = System.IO.Path.GetFileName(dialog.FileName);
string ApplicationPath = Application.StartupPath.Substring(0, Application.StartupPath.Length - 10); 
ApplicationPath = ApplicationPath.Replace("\\", "\\\\"); 
string consultant_logo = ApplicationPath + "\\images\\" + correctFileName3;

I want to insert consultant_logo as a string in the database by this insert query:

 String insertQuery = "Insert Into ubc.projectinfo(`projectName`, `companyName`, `projectNumber`, 
`projAddress`, `nameOfConCompany`, `consultantPhone`, `nameOfEng1`, `nameOfEng2`, `nameOfEng3`, 
`consultantAddress`, `phoneEng1`, `phoneEng2`, `phoneEng3`,`ubc_logo`, `company_logo`, `consultant_logo`)
 VALUES ('" + projectNameText.Text + "','" + companyNameText.Text + "' ,'" + projectNumber.Text + "','"+
 projAddress.Text+ "','"+ nameOfConCompany.Text+ "','"+consultantPhone.Text+"', '"+ nameOfEng1.Text + "',
 '"+ nameOfEng2.Text+ "', '"+ nameOfEng3.Text+ "' , '"+consultantAddress.Text+"','"+ phoneEng1.Text+ "', 
'"+ phoneEng2.Text + "','"+ phoneEng3.Text + "', '"+ubc_logo+"','"+company_logo+"', 
'"+consultant_logo+"')";

but this error occurred "you have an error in your SQL syntax";

Is there a particular reason, why you don't use prepared statements like this https://stackoverflow.com/questions/11070434/using-prepared-statement-in-c-sharp-with-mysql — nbk, Feb 16 '20 at 12:51
Using a parameterized query will save you from most syntax errors because you don't need to worry about placing every quote and bracket correctly. Also, it avoids SQL injection vulnerabilities. However, if you know how to write SQL, and you look at the value of `insertQuery` while debugging you should be able to see what is wrong. — Crowcoder, Feb 16 '20 at 13:06
@nbk I tried to use prepared() this but the error still occurred — Sarah, Feb 16 '20 at 13:11
@Crowcoder error syntax not in quotes, it is in path string, when I replace this path value with any text it inserted correctly — Sarah, Feb 16 '20 at 13:13
show us your code for prepared statement and also the complete error message. As far as i know there is nothing in a filepath, that you can't save it as text directly without manipulation in your database — nbk, Feb 16 '20 at 13:15
There is a problem with quoting when combined with the values you are concatenating otherwise you wouldn't get a syntax error. — Crowcoder, Feb 16 '20 at 13:18
Gluing bits of data into strings to make SQL is just wrong. There are well documented and well publicized dangers related to it only one of which is syntax errors. NET has always provided for SQL Parameters for nearly 20 years now. — Ňɏssa Pøngjǣrdenlarp, Feb 16 '20 at 15:07
most likely.. one of the variables you're blindly passing in already has a quote in there, throwing off your whole literal concatenation. — Napoli, Feb 16 '20 at 15:38

score -1 · Answer 1 · edited Jun 20 '20 at 09:12

Save image path in database - bad idea. Every time, when you transfer you program, you should copy folder with images. I recommend you to save images as byte array into database - not path.
Try to use new syntaxys of string in C#. Like this:

string query = $"INSERT INTO 'testTable' ( 'testColumn' ) VALUES( { youVariable } )";
In MySQL query you don't need to mark the variable with quotation marks like this:

"VALUES ( 'myVariable1', 'myVariable2' )" - wrong

"VALUES ( myVariable1, myVariable2 )" - true

When you use "dialog.FileName" you already have file path and you don't need to get it again with System.IO;

So try this query:

insertQuery = $"Insert Into ubc.projectinfo ('projectName', 'companyName', 'projectNumber', 'projAddress', 'nameOfConCompany', 'consultantPhone', 'nameOfEng1', 'nameOfEng2', 'nameOfEng3', 'consultantAddress', 'phoneEng1', 'phoneEng2', 'phoneEng3','ubc_logo', 'company_logo', 'consultant_logo') VALUES ( {projectNameText.Text}, {companyNameText.Text}, {projectNumber.Text}, {projAddress.Text}, {nameOfConCompany.Text}, {consultantPhone.Text}, {nameOfEng1.Text}, {nameOfEng2.Text}, {nameOfEng3.Text}, {consultantAddress.Text}, {phoneEng1.Text}, {phoneEng2.Text}, {phoneEng3.Text}, {ubc_logo}, {company_logo}, {consultant_logo})";

Insert full path of images into MySQL using c#

1 Answers1