From: arne@vajhoej.dk   
      
   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:   
      
      
   Defense Option 4: STRONGLY DISCOURAGED: Escaping All User-Supplied Input   
      
      
   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:   
      
      
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)