So my attempt to provide an ultra low cost IOT serial to Web adapter continues. I’ve been able to accomplish streaming very large files (73K lines, 2MB) thru a $5 ESP-8266 device to a $10 CNC motion controller, but growing the working hardware into a general purpose solution is probably beyond me just because of the the server security issues. e.g. how to setup an SQL server so that it can bulk import a file to a table without giving the public user so many rights that they can also just hack the server.
HELP!? Anyone who knows SQL security, even a little would be a lifesaver. I’d be willing to pay much less than you are worth for the assistance! Or be eternally grateful.
Use PHP to upload the file, then a bash cgi script that ingests the file and stuffs it into a MySQL database record. Or encode the file first to help prevent sanitation issues and put the encoded version of the file in the SQL record directly from PHP. All the above is hacky, but that’s how I would do it. Also, don’t leave servers open to the Internet if you can help it. It’s a good way to get compromised. Perhaps force the user to log in before doing anything, something easily done with a .htaccess file.
@Kienan_Vella I’m a little confused how a server is any good at all if it isn’t open to the internet… you mean don’t allow just anyone to upload the file? Ah, yes, of course; Only I will be able to upload files during this phase.
I might someday offer a service… In the past, I’ve had open FTP ports where anyone could upload files to me, and that’s fine, as long as they have no way to get the file back. e.g. they can’t use it to host porn or whatever. Never had any issues. In this case, they would only be able to get a line at a time back, and it would be in plain text only e.g. http://techref.massmind.org/techref/getline.asp?line=1&lines=10
but they will probably still upload crap… I could reject anything with other than ASCII in it to prevent garbage, and strictly limit file names (no uploading file = ;DROP TABLE or whatever), and go around plugging holes in general, which I will still do.
But honestly my intent is to come up with something that people put on their OWN servers… something easy to spin up on gcloud or whatever. And then only they will access it.
I hope you will pardon the newbie question, but I have almost no experience with LAMPS (being a M$ guy) so I’m curious about this bash script… can that somehow be triggered from the web server? E.g. can you cause it to be invoked when the user hits a web page, perhaps the upload page, and hand off the file name to the script?
Yep, by using CGI scripts in pretty much any programming language, you can have the web server run a local script when the user loads a specific page. in this case, PHP would handle the file upload to a directory, then when the upload was finished it would automatically run the CGI script, then automatically go to a success/fail page on script exit.
You wouldn’t, by chance, have experience with google cloud hosting of such sites? And be stunningly desperate to make less money than you are worth? ,o)
This sounds similar to ChiliPeppr’s cayenn protocol. Serial port json server 1.93 in github has the ability to relay tcp and udp commands to esp8266’s to upload commands. It also has an announce protocol so devices can convey their presence.
I have no experience with Google cloud as a web hosting platform.
I don’t exactly have the time at the moment to whip up such a system (and as noted, this solution is pretty ugly, and there is likely a better way to do it).
I’d suggest that if this is something you intend to use, maintain and distribute that it’s worth the learning curve for future troubleshooting. It’s pretty easy to find excellent tutorials for PHP, bash, and MySQL, and there are tons of examples.
@jlauer The difference is: Your computer doesn’t need to install a program, and your browser doesn’t need to be talking to the device. You can use your local browser to tell the device to stream the file from the server, and then the device takes off and just does it. You can go watch porn and check back from time to time to see if it’s done (or the server can send you a notification).
@Kienan_Vella Yeah… on the other hand, a lifetime of learning MS is out the window. Maybe I can trigger a batch file and use SQL CMD to do it via M$… Sorry… I’m just not ready to convert yet… maybe I’ll get there, and in any case, thank you very much for the idea, I REALLY appreciate it.
Just to throw something else into the mix, I don’t really think you are going the right direction. An authenticated service would be a better route over some general purpose web server. You could include two way public/private key tokens for authorizing and pretty much do what you want after connection established. It is about the same as a pop server. You could get fancy and wrap it in some XML stuff but I don’t really see much reason to add the database server to complicate your life over flat files. It’s not like you are going to keep the data around and use any database functionality. You are just queuing up some files to process.
@David_Kirtley Authentication {edit: of who can upload a file} is down the road… for now, I’d just like to get something testable working locally. The reason for using a database is this: It can very very quickly return line X of a very large ASCII file. G-CODE files can be in the Gigabytes. The little ESP 8266 can not host that size file locally. It CAN ask a server for line X of a gigabyte sized file and deal with the data in that one line… then ask for the next. If there is a better way to get line X quickly from a very large text file, I’m certainly open to that.
Security should never be an afterthought. Always design with security and access restrictions in mind from the ground up. The esp8266 sounds like a bad tool for this job. Get a raspberry pi or something else that can do the processing on it and output over serial. Octopi/octoprint basically does this already.
Thing is, you are not doing random access on the file. You are just processing it sequentially. It is just a file pointer. You really wouldn’t have to worry too much about authentication. Just pass a token at the beginning of the file and if it’s not a valid token, send it off to the bit bucket. You can also send other info as a preamble such as what is the destination machine, when should it run and such to enable expanding to more devices. The proper designation would be a chat service. Basically, you are re-inventing the POP/SMTP protocol from your description. The file containing G-code rather than email text is just a matter of context. You could store it in a DB like Exchange does which is perfectly fine but I don’t see it gaining you anything.
@Kienan_Vella :sigh: you have completely misunderstood the situation so apparently I have failed to explain it. There IS NO security issue between the server and the ESP. There IS NO security issue between the web server and the SQL server. The issue is that the SQL server will not bulk import an ASCII file without releasing so many permissions that it WOULD become a security issue. I’m specifically posting this, specifically asking for help BECAUSE “Security should never be an afterthought” and BECAUSE I DO “Always design with security and access restrictions in mind from the ground up.”
@David_Kirtley Interesting comparison between SMTP and what I’m trying to do. Yes, I suppose I could parse each line of the text file into a separate email, then have the ESP implement a simple SMTP server and send the text of each “email” received to the connected serial device. But that’s exactly what I’ve already accomplished with the SQL server, and I would still have the same permissions issues with injecting those fake emails into the exchange server that I have with the SQL server, no? I’m assuming that’s what you meant when you said “I don’t see it gaining you anything”
@Kienan_Vella :facepalm: Yes, I see the confusion now. I didn’t mean authentication of the web server to the SQL server was down the road, I meant that authentication of who was allowed to upload the file in the first place. At this point, only I can insert a file onto the server. In the future, I will tackle the ability for auth users to upload files. That sort of thing is easily accomplished and not an issue I would ask for help with.