Re: VMS x86-64 database server

On 7/8/2025 7:38 PM, Lawrence D'Oliveiro wrote:

On Tue, 8 Jul 2025 18:40:31 -0400, Arne Vajhøj wrote:

Dynamically creating SQL string where the dynamic part is for data is a
security disaster waiting to happen (and possible poor performance as
well).

 That’s a pretty naïve statement to make.
 Quoting literal data in standard SQL is quite simple: turn the data into a
string literal with single quotation marks, and any embedded single
quotation marks are written twice. That’s it. Every other character can be
represented as itself, literally.

It is an assumption that all developers remember to do it right. Problem
is that developers are humans - they make mistakes. Not every time. If
they are okay then not even often. But a few times. If there are 1000
places in the code where escape should be used, then there is a pretty
good chance that it will be forgotten at least 1 time.
There is a reason why OWASP when it comes to preventing SQL injection
states:
<quote>
Defense Option 4: STRONGLY DISCOURAGED: Escaping All User-Supplied Input
</quote>
Furthermore the escape problem is a little more tricky than
what you describe.
Very few API's does not allow prepare/parameters, but a few
wellknown examples do exist: the recently discussed DBLIB (which
is one of the reasons why it was replaced by CTLIB in 1993 - 32
years ago) and the old PHP mysql extension (replaced by mysqli
extension in 2004 and PDO in 2005).
Because mysql extension did not support prepare/parameters
they first added a mysql_escape_string function to do what one
think should be done.
$s = mysql_escape_string($s);
But clever people found out that the argument list was
wrong.
It should have connection reference to correctly handle
escape with various more exotic character set. So
mysql_real_escape_string function was invented.
$s = mysql_real_escape_string($s, $con);
For those that still have PHP 5.x and a MySQL old enough
to work with PHP 5.x then try:
<?php
error_reporting(E_ERROR);
$con = mysql_connect('192.168.0.10', 'root', '');
mysql_select_db('test', $con);
// nothing - all good
$s = 'BB';
$rs = mysql_query("SELECT COUNT(*) AS n FROM t1 WHERE f2 = '$s'", $con);
$row = mysql_fetch_array($rs, MYSQL_ASSOC);
echo 'nothing - all good: ' . $row['n'] . "\r\n";
// nothing - injection attempt
$s = "BB' OR '1'='1";
$rs = mysql_query("SELECT COUNT(*) AS n FROM t1 WHERE f2 = '$s'", $con);
$row = mysql_fetch_array($rs, MYSQL_ASSOC);
echo 'nothing - injection attempt: ' . $row['n'] . "\r\n";
// bad escape - all good
$s = 'BB';
$s = mysql_escape_string($s);
$rs = mysql_query("SELECT COUNT(*) AS n FROM t1 WHERE f2 = '$s'", $con);
$row = mysql_fetch_array($rs, MYSQL_ASSOC);
echo 'escape - all good: ' . $row['n'] . "\r\n";
// bad escape - injection attempt
$s = "BB' OR '1'='1";
$s = mysql_escape_string($s);
$rs = mysql_query("SELECT COUNT(*) AS n FROM t1 WHERE f2 = '$s'", $con);
$row = mysql_fetch_array($rs, MYSQL_ASSOC);
echo 'bad escape - injection attempt: ' . $row['n'] . "\r\n";
// bad escape - serious injection attempt
mysql_set_charset('GBK', $con);
$s = "BB\xbf\x27 OR 1=1 #";
$s = mysql_escape_string($s);
$rs = mysql_query("SELECT COUNT(*) AS n FROM t1 WHERE CONVERT(f2 USING GBK) = '$s'", $con);
$row = mysql_fetch_array($rs, MYSQL_ASSOC);
echo 'bad escape - serious injection attempt: ' . $row['n'] . "\r\n";
// correct escape - serious injection attempt
mysql_set_charset('GBK', $con);
$s = "BB\xbf\x27 OR 1=1 #";
$s = mysql_real_escape_string($s, $con);
$rs = mysql_query("SELECT COUNT(*) AS n FROM t1 WHERE CONVERT(f2 USING GBK) = '$s'", $con);
$row = mysql_fetch_array($rs, MYSQL_ASSOC);
echo 'correct escape - serious injection attempt: ' . $row['n'] . "\r\n";
// fucked up correct escape - serious injection attempt
mysql_set_charset('latin1', $con); // we have to reset
mysql_query('SET NAMES GBK');
$s = "BB\xbf\x27 OR 1=1 #";
$s = mysql_real_escape_string($s, $con);
$rs = mysql_query("SELECT COUNT(*) AS n FROM t1 WHERE CONVERT(f2 USING GBK) = '$s'", $con);
$row = mysql_fetch_array($rs, MYSQL_ASSOC);
echo 'fucked up correct escape - serious injection attempt: ' . $row['n'] . "\r\n";
mysql_close($con);
?>
nothing - all good: 1
nothing - injection attempt: 3
escape - all good: 1
bad escape - injection attempt: 0
bad escape - serious injection attempt: 3
correct escape - serious injection attempt: 0
fucked up correct escape - serious injection attempt: 3
If you escape manually then you will live to regret it.
Arne