Re: Insert into mysql database

[NorCal12]

Thanks for your comments. I ran things with your suggested language and it flagged the issue. Most sales involve pounds of quota, but when one sells fishing gear for example, there are no pounds. In that case the pounds field went into the database as null. All of my tests were using fishing gear and the newer version of mysql was not as forgiving as the older version. But at least I now know what needs to be done.

As far as using PHP on the site, I do use it on some presentation tables displaying past sales. But for my current issue that is not involved.

[Radiola]

In case it’s not clear from the references to placeholders and Little Bobby Tables, the way your query works is very dangerous. Someone who knows what they’re doing could cause your database to run their SQL statements with the privileges of your CGI program.

The problem is that the backslash escaping doesn’t escape everything — in particular, not the input from the form. If an attacker doctored up a form submission so that it included their own SQL in the right place, and fixed up the front and back of that string so that the whole combination of your SQL query plus whatever they submitted in $form{'ITEM'} still resulted in a valid series of SQL statements, your DB will cheerfully run the whole thing. An attacker could potentially steal customers’ private data, submit bogus transactions, or clear the whole database.

It’s error-prone at best to pre-process $form{'ITEM'} to escape or strip stuff like that. The placeholders that hippo and LanX refer to solve the problem by telling the DB that your data is only data and not to run it no matter what it looks like.

You really need to do this, especially if you’re dealing with money. It might be some work now, but it will correct a very dangerous problem, and your code will likely end up easier to read (and write) to boot.

[LanX]

> But at least I now know what needs to be done.

I hope you know that using SQL-placeholders are the easiest, most robust and safest solution.