Pi Hardware

funny, but obviously you don't understand sql as the sample code specifically cannot do that kind of thing. That is the reason why the SQL command is not passed. And it is the reason why all the arguments are 'quoted'.

You mixed up _GET and _POST, there are no sanity checks and you just dump it in the sql string. What if _POST[$name] starts with '; ? The key is to use mysqli_real_escape_string($dblink, $strval) or the equivalent for your db.

Here are some snippets I wrote back in the day, maybe could use corrections as well? Idk. And I got out before everything turned into classes. Also this is so old that the mysql extension still existed (as opposed to mysqli).

if (get_magic_quotes_gpc()) $postdata = stripslashes($postdata); if ($notags) $postdata = strip_tags($postdata); if ($collapsewhitespace) { $postdata = preg_replace('/^\s+/', '', $postdata); // not sure why I didn't use trim() $postdata = preg_replace('/\s+$/', '', $postdata); $postdata = preg_replace('/\s+/', ' ', $postdata); } $postdata = mb_substr($postdata, 0, $maxlength); // avoid string or field length overflow if ($nohtml) $postdata = htmlspecialchars($postdata); // tricky; could lead to double encoding if not careful

$inidata = @parse_ini_file($inifile, TRUE); // load from outside doc root $ln = @mysql_connect($inidata[$section]['server'], $inidata[$section]['username'], $inidata[$section]['password']); $db = @mysql_select_db($inidata[$section]['database'], $ln); @mysql_query("SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_520_ci'", $ln); unset($inidata); unset($inifile);

if (isset($_SERVER['HTTP_REFERER'])) { $refer_prev = $_SERVER['HTTP_REFERER']; $ref = parse_url($refer_prev); if (strcmp($ref['host'], $_SERVER['HTTP_HOST'])) $err .= 'Request from different host [' . htmlspecialchars($ref['host']) . '] not accepted.' . "\n"; }

/** * Insert or update one row in a database table by ID. If ID is positive it is the * unique ID of the row to update. If zero, a new row is inserted. If negative, a new * row is inserted using INSERT IGNORE (no error message when insertion fails due to * key constraints). Data for the insert or update is expected in an associative array * of key-value pairs: fieldname and unescaped and unquoted fieldvalue. The function either * returns success of the update, the ID of the newly inserted row, zero if no row was * inserted (INSERT IGNORE failure) or FALSE on error. * @param string $table tablename * @param integer $id unique ID of row to update or zero for new insertion or negative for INSERT IGNORE * @param array $keyvaldata associative array of key-value data * @return mixed boolean update success, integer insert ID or zero, boolean false */ function db_putrow($table, $id, $keyvaldata, $escape = TRUE, $empty2null = TRUE) { //check parameter validity if ( empty($keyvaldata) || !is_array($keyvaldata) || !count($keyvaldata) ) return FALSE;

//check connection if ( !$ln = db_connect() ) return FALSE;

//implode key-val array to partial SQL statement, escape and quote values $assign = array(); foreach ( $keyvaldata as $k => $v ) { if ( $empty2null && !strlen($v) ) $v = 'NULL'; elseif ( $escape && !is_numeric($v) && strcmp('NULL', $v) && !preg_match('/^[A-Z0-9_]+\(.*\)$/', $v) ) $v = "'" . mysql_real_escape_string($v, $ln) . "'";

$assign[] = '`' . $k . '` = ' . $v; }

$sql = '`' . DB_TABLEPREFIX . $table . '` SET ' . implode(', ', $assign);

if ( $id > 0 ) //update particular row, return update success { $sql = 'UPDATE ' . $sql . ' WHERE `id` = ' . $id . ' LIMIT 1'; if ( mysql_query($sql, $ln) ) { //return number of affected rows or TRUE for successful query if ( $n = mysql_affected_rows($ln) ) return $n; else return TRUE; } else return FALSE; } elseif ( $id == 0 ) //insert new row, return new row ID or FALSE on error { $sql = 'INSERT INTO ' . $sql; return mysql_query($sql, $ln) ? mysql_insert_id($ln) : FALSE; } else //insert new row (possible duplicate), return new row ID or zero on failure or FALSE on error { $sql = 'INSERT IGNORE INTO ' . $sql; return mysql_query($sql, $ln) ? (mysql_affected_rows($ln) ? mysql_insert_id($ln) : 0) : FALSE; } }

That is insufficient protection.

Precisely.

Doesn't mysql provide prepared statements with placeholders like sqlite does ? Those are the safest and easiest way to put user date into SQL.

I don't know, maybe now it does. What I posted was my way of doing just that.

I adapted it from a POST script

if _POST[$name] starts with '; you will get an sql error whem sql encounters set field = '';'

wrong

Of course. I was merely illustrating the principle. using https and curl with a password means the only person sending is your script anyway.

It is unlikely that the NSA would wish to destroy your climate data, though for sure the eco warriors would want to make it scarier. But they are crap at everything including SQL.

There are a dozen ways of protecting against data corruption and malware available to any coder who can code and understands te REAL risk (as opposed to armchair security experts who think the NSA is going to hack an amateur weather database ..

I don't claim to specialize in SQL, but I've done my share, however it's your bugs being discussed not mine.

Can I smuggle a single-quote and a semicolon into the $_POST[] array, so that you concatenate it onto your query string thinking it's merely a value? Yes I can.

If you think those mitigate SQL injection attacks you are badly mistaken. Your code could use a fixed query string referencing @variables which are initialised with the values passed.

Who needs three letter agencies? There are enough bots out there throwing attacks at every open port they can find to see what happens...

Is that the same as a prepared statement, as used by JDBC or (IIRC) ODBC interface modules? Prepared statements are designed specifically to protect your database against injection attacks

If your DBMS supports database procedures, using them is also a good way to avoid injection attacks. For private projects I've pretty much standardised on using PostgreSQL because its very stable and has excellent self-managing capability, including the ability to handle database changes associated with new software versions. I always update tables via JDBC using prepared statements, but tend to retrieve data via views when using less secure scripted languages, e.g. Perl.

no. Try it

It could.

if they are throwing attacks at https ports the internet would stop working

What do you think will stop it?

As far as I can see you have an absolutely standard SQL injection vulnerability.

On Sat, 12 Sep 2020 08:04:27 +0100, Ahem A Rivet's Shot declaimed the following:

It does since sometime in v4, but the API is a terror. One has to populate a structure defining what the fields are (including datatypes, buffer addresses, lengths for strings), invoke separate prepare and execute calls.

The older API, and many of the adapters for scripting languages, relied upon the client end quoting/escaping parameters and sending the command entire.

Den 2020-09-12 kl. 13:35, skrev Martin Gregorie:

I don't think so. Prepared statement has been around longer than the web-form. Prepared statements are used if you don't want the database to create a new execution path every time you execute a statement where only the parameters are changed. For at least Oracle, it is a way to keep the statement in the SGA cache. It is all about performance. That it is safer for webforms may be good - but not the reason it exists

"PREPARE creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance."

When I started coding professionally I learned that PREPARE is the way to go - no concatenating strings to a statement. This was in 1997. The code base suggest that Sql.Prepare in our sql module had been around for many years already then.

And then you have a hard time to switch database. Keep business logic in code and traceability in triggers. At least I find that to be a sound principle.

Agreed: I've never used it with a modern DB - I remember using the DEC equivalent, which I seem the remember as about to only way you could interface the DEC RDBMS from COBOL on a VAX, on the only VAX-based project I ever worked on. I may also have used the odd database procedure with Postgres 9 on the small NCR Unix boxes we used to to host ATM networks, but don't really remember because I did far more work on the network side of those (X.25, not TCP/IP!).

But, when all you had was a choice between assembling SQL statements with sprintf() statements or using database procedures then you used the latter if hackery was a possibility.

IIRC the early ODBC modules didn't support prepared statements, but I might be wrong about that.

Also a good way to ensure vendor lock-in.

-- Steve O'Hara-Smith | Directable Mirror Arrays C:\>WIN | A better way to focus the sun The computer obeys and wins. | licences available see You lose and Bill collects. |

Den 2020-09-12 kl. 20:47, skrev Ahem A Rivet's Shot:

Unless you write a glue layer (which I always do to encapsulate a 3rd party component - at least on the server side)

Something like (extremely simplified - it is usually a couple of files per db, with different scope where you set a bind varible compared to where you use prepare/execute)

pseudo-language:

void Prepare() switch (db) { postgres : {prepare the postgres way, and save pointers to variables} oracle : {prepare the oracle way, and save pointers to variables} whatever : {prepare the whatever way, and save pointers to variables} }

Den 2020-09-12 kl. 19:54, skrev Martin Gregorie:

Interesting, I did not know the VAX thing. I'm pretty sure we used RDB on VAX before I was employed, but I'm not sure if they used prepare or not.

My main reason to reply was just to say that I think performace was the reasson for prepare - not to prevent injection.

RDB - That was the name I was trying to remember!

At the time (late -80s, early 90s) the only interface between DEC COBOL and RDB was a procedure you wrote, using its own language. It provided COBOL-compatible call interfaces and a way to specify the query to RDB - using a sort of pseudo-English that EC called Structured Query Language, nothing like the semi-mathematical syntax of SQL as we know it.

The DEC SQL module was then compiled and staticlally linked with the COBOL program that called it.

In short, if you ever used IDMS, which preceded the first Codd's first RDBMS implementation you'd not miss the similarities between the way that IDMS and RDB handled the interface to their respective databases.

In the flavour of IDMS (ICL IDMSX on the 2900) I used, the DB Admin used an IDMS schema preprocessor to generate a COBOL module from the schema. This provided the interface between your program and the database server.

When you wrote your COBOL application you specified DB access using a set of COBOL statements that contained IDMS-specific verbs and that accessed COBOL variables. A second preprocessor converted these statements into compilable COBOL that called the code generated by the IDMS schema processor. This was a very nice way to do it from the application programmers viewpoint since all the code he wrote looked exactly like normal COBOL, though with a few additional verbs.

Sounds reasonable, and very useful when it also turned out to be a good way to nullify SQL injection attacks.