Category: Coding

SQL Injection

This is what I do for every field that I read from a form or from the URL if I am going to use it in a database query.
The mysql_real_escape_string looks for things that could be used to subvert a query and escapes them.
When I create my SQL query statement they can’t be treated as part of the command.

This is a part of a file that reads and validates a form

  // Look to see if the checkbox has a value, then update the database
  if ($id != '') {
    $NickName = mysql_real_escape_string($_POST["NickName$i"]);
    $Address = mysql_real_escape_string($_POST["Address$i"]);
    $Method = mysql_real_escape_string($_POST["Method$i"]);
    
    $Name = mysql_real_escape_string($_POST["Name$i"]);      
    $Code = mysql_real_escape_string($_POST["Code$i"]);
    $TimeZone = mysql_real_escape_string($_POST["TimeZone$i"]);
  }

I have Javascript that validates the data in user forms but I always validate again on my end before I put it into the database.
    $numToUpdate++;  
    $data_is_valid = TRUE;
    if (!$DeleteRecipients) {
      include('address_book_validation.php');
    }

This is what the escape string looks like for URLs e.g.
http://wellgolly.com/index.php?p=Reminder&m=Pricing

  
  $page  = mysql_real_escape_string($_GET['p']);
  if ($page == '') { $page = 'Main'; }

  $menuChoice = mysql_real_escape_string($_GET['m']);
  if ($menuChoice == '') { $menuChoice = 'New'; }

Important note:
mysql_real_escape_string() calls MySQL’s library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ‘, ” and \x1a.

Since mysql_real_escape_string is a mysql command, not a php command it requires an active database in order to run. If a database session is not running, it will create one. From the manual, The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

If you escape every get command, you can actually cause your server to be inaccessible because the number of database connections will be exceeded. This happened to us when being scanned by Bing recently. The bot made so many requests to a file with multiple escape strings, that it blocked all other connections.

Update: This post uses mysql_real_escape_string but they now recommend using mysqli_real_escape_string. I haven’t looked into what is required to make the update.

What languages do you know?

One of the ‘How to tell if someone is a programmer’ jokes is to ask someone what languages they know. These would be popular answers.

Languages

I’m not really a programmer, but I know a fair number of these.

I went to a C class in 1988 but never had a reason to use it. I really learned C one summer a few years ago when I had some kids to help me, but I’ve never used it. It’s pretty good at making you think.

I can read and debug Java but can’t write it.
No reason to use C++.

I use PHP all the time and I’m at the point where I think I need to start using classes.

Absolutely no reason to go anywhere near C# or Visual Basic.

Python and Perl seem like they would be useful, especially for some of the string manipulations I do with sed.
I’ve played with Python a bit and it looks like something I’d like if I had the time to learn it. Perl is {$}}@##}}

Learning JavaScript.
Spent an hour today figuring out that <> is not used in JavaScript—it’s !=

Upgrading to the latest ActionScript.

Haven’t used SAS for 20 years but liked it a lot when I used it.

Of course I use SQL all the time—how else do you get to your data?

Update: Spring 2014
I learned Objective-C—so I can convert some programs to the iPad. It’s a really nice language and Apple has lots of API that make it easy to develop apps.

Not on the list, but I just spent a while learning Bash shell scripting. It comes in handy when maintaining Linux boxen.

Update: Winter 2016
I’ve heard all of the jokes about Perl being an unreadable mess, but I have been updating a site that is built with Perl and I must say that it isn’t bad. The language has some quirks but I have been able to read the code without much trouble and make changes to the web pages without spending a whole lot of time learning a completely new language. I even wrote a couple of new scripts that worked the first time.

Update: Fall 2016
I just read The Swift Programming Language and have started watching CS193P with Paul Hegarty. The language is completely different from anything I’ve used before. It is a shining example of what a talented group of programmers can do when they do not have to maintain backward compatibility with previous languages (e.g. Objective C and C) or fit into an existing paradigm of how object oriented languages must behave. I really like working with it in the Playground and can’t wait to make real apps with it.

More things I can’t remember.

Manipulate a phone number

To change a phone number from a string of digits, e.g. 8051230123 to a formatted value e.g. 805.123.0123 you can use the following grep pattern to parse the string


([0-9]{3})([0-9]{3})([0-9]{4})

and

\1\.\2\.\3

to replace the parsed string with dots.

Tar up a folder

Someone wrote this up for me years ago and I still refer to it from time time.

Assuming you want to copy the site onto your local machine:
The following will tar and compress up the entire www tree and
put the archive file in your home directory (on the server).

cd /; tar -czvf ~/www.tgz www

to decipher:
tar – tape archiver
-c – create new archive, write supplied files too it
-v – verbose – print out names of files as they are added to archive
-z – compress archive using gzip (gnutar only, as used on OS X)
-f ~/www.tgz – write output to file www.tgz in home directory
www – the file to include in the archive

To extract it again:


cd /where/you/want/it; tar -xzvf /path/to/www.tgz

will unarchive the www tree under the directory /where/you/want/it

Wordlist

My favorite words list has formatting in the CSS to make the first word of the definition (the part of speech) capitalized.


.definition p:first-line {
  font-weight: bold;
  font-variant: small-caps;  
}

Some words can be used as Verbs and Nouns, or Verbs and Adjectives, so I need to change the formatting of the part of speech.


<span class="boldSmallCaps">Adjective</span><br />

So the part of speech is now formatted with this CSS

.boldSmallCaps {
  font-weight: bold;
  font-variant: small-caps;  
}

Frequencies for equal-tempered scale

Link

Prevent your ISP from redirecting mis-typed URLs

My ISP started to hijack mis-typed URLs and open up their search engine page—which makes it difficult to correct typos. You can bypass your ISPs DNS server by using OpenDNS (208.67.222.222 , 208.67.220.220) or Google Public DNS (8.8.8.8 , 8.8.4.4 ). If you are using OSX, open up the Network Panel in System Preferences and then click on the Advanced button. In the DNS tab, replace the default IP address with one of the ones above.

Block access to javascript from Tynt, the Copy/Paste Jerks

For my word of the day I frequently copy a sentence with the target word and lately I’ve noticed that I get the URL with it. It’s annoying to delete the URL and apparently it annoys John Gruber too. The solution is to edit your /etc/hosts file and block their server. Add this line to the end:
127.0.0.1 tcr.tynt.com

Make files invisible

SetFile file -a V

phpMyAdmin Notes

I ran across a couple of things when uploading data to our MySql database that kept me puzzled for a few hours. I was trying to load data from the FAA on airports. There are 19,755 airports in an xls file. Last month I converted the records to semi-colon delimited fields and imported them without a problem. This month I wanted to add a few more fields, so I thought I’d just create the new fields and import the data in the same step. I used

INSERT INTO `Airports_NFDC` (`id`, `NFDC_id`, `type`, 
`FAA_id`, `state`, `city`, `name`, `owner`, `access`,
 `lat`, `lon`, `elev`, `var`, `TPA`, `DistCBD`, `DirCBD`,
 `Activation`) 
VALUES

to insert the data. However, it appears that this takes forever and my query would time out without importing any records. (It could be that something else was causing the termination, but I managed to get 5,000 records to import, so I’m reasonably certain that it is a transaction time issue.)

I converted the data to CSV delimited by a ‘;’ and within a minute the file was uploaded. One glitch occurred. It gave me an error on the last record. The last record did not have an end-of-line marker and the importer didn’t know that an end-of-file must mean that the line had ended as well. Inserting a return after the last line fixed the problem.