Unknown column 1

[penguinspeaks]

My statement is a simple one:

STRSQL = "UPDATE BRACKET16_"&tid&" SET NAME2 = "&TNM12&" WHERE POS = 17"
conn.execute(strSQL)

Pretty straight forward right?? However, when it is executed I get"

[MySQL][ODBC 3.51 Driver][mysqld-5.6.33-log]Unknown column 'Bob' in 'field list'

/STARTTOURNEY.ASP, line 49

When I run response.write I get the correct response :

UPDATE BRACKET16_73 SET NAME2= Bob WHERE POS = 17

The TNM12 variable returns the correct response of Bob when I do a response.write("TNM12")

Why is the statement looking for a different column name than NAME2??

Please keep in mind that I am using classic ASP with MySQL database. Not sure if that information helps or not.


Thanks,
Penguin

 

[Olaf Doschke]

If you want to set NAME2 to 'Bob', then you have to do that and not set it to Bob. MySQL understands you want to set NAME2, but not, that you want it to be the string 'Bob', but a variable or field called Bob.
You know what you need to do, your ASP/VB.NET knowledge is there already, when you set the STRSQL variable with a quoted string (even multipart put together with other variables), so you know how to assign a value to a variable and you know strings need to be quoted.

Overall, you have a bigger problem in setting values that way, it opens up the chance of SQL injection.
Imagine TNM12 is something I may enter into an HTML form input text element and you put it in like that, imagine I enter the following:
'; DROP DATABASE; --
or in this case, if I see that error and know Bob is taken as a field name, I could also try
''; DROP DATABASE; --

Have a read on the topic of SQL injection.

Entry Level explanation:

https://www.w3schools.com/sql/sql_injection.asp

A good reference and more general security-related site:

https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

It's the hard way to not only learn about how to put together valid SQL but also protect against malicious input. I'm saying you might put this back for later and first get your code going, but only if this is for personal use and not public. w3schools has some examples about using the best approach to protect against that with parameterization of requests for ASP.NET

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[penguinspeaks]

Thank you for the response. I am trying to expand on someone else's code. I, after making everything work, will go back and change the communication with the database to better protect against injection. Also, this will be turned into an app. There are no instances on any form where a user, other than admin, can enter anything on a form. All input will be derived from dropdown menus.

Now about my issue. I am feeling foolish now that I didn't see the lack of ' in the code. Thank you for pointing that out. Sometimes an issue that you rack your brain over, at times, can be the simplest of things when recovering from a stroke.

Thanks again for the response and for pointing me in the right direction for sql injection.

Thanks,
Penguin
Please keep in mind that I am using classic ASP with MySQL database. Not sure if that information helps or not.