10 Replies - 744 Views - Last Post: 06 June 2009 - 02:56 PM Rate Topic: -----

#1 Mike007  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 7
  • View blog
  • Posts: 332
  • Joined: 30-August 07

Escaping SQL Strings

Posted 05 June 2009 - 05:23 PM

Hello, I have been trying to find a method that escapes sql statements in a Java String with no luck so far. I am using SQLLite and the standard java.sql package. I know org.apache has that in and a lot more, but I rather not download another library if possible. Any thoughts?
Is This A Good Question/Topic? 0
  • +

Replies To: Escaping SQL Strings

#2 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8334
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Escaping SQL Strings

Posted 05 June 2009 - 05:58 PM

View PostMike007, on 5 Jun, 2009 - 04:23 PM, said:

a method that escapes sql statements

What the heck does that mean ?
Was This Post Helpful? 0
  • +
  • -

#3 Mike007  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 7
  • View blog
  • Posts: 332
  • Joined: 30-August 07

Re: Escaping SQL Strings

Posted 05 June 2009 - 06:02 PM

To prevent SQL injections you have mysql_real_escape_string() function in PHP. I want to find something similar for Java that's all.
Was This Post Helpful? 0
  • +
  • -

#4 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8334
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Escaping SQL Strings

Posted 05 June 2009 - 06:07 PM

View PostMike007, on 5 Jun, 2009 - 05:02 PM, said:

To prevent SQL injections you have mysql_real_escape_string() function in PHP. I want to find something similar for Java that's all.

Java has a nice feature called PreparedStament that is the standard way of preventing SQL injection in Java
Was This Post Helpful? 0
  • +
  • -

#5 Mike007  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 7
  • View blog
  • Posts: 332
  • Joined: 30-August 07

Re: Escaping SQL Strings

Posted 05 June 2009 - 06:15 PM

Ok thank you seems to be what I was looking for.

Is there also a standard way to encode and decode html entities to characters ('&' -> '&') ?
Was This Post Helpful? 0
  • +
  • -

#6 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8334
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Escaping SQL Strings

Posted 05 June 2009 - 06:18 PM

View PostMike007, on 5 Jun, 2009 - 05:15 PM, said:

Ok thank you seems to be what I was looking for.

Me or my answer ? :D

View PostMike007, on 5 Jun, 2009 - 05:15 PM, said:

Is there also a standard way to encode and decode html entities to characters ('&' -> '&') ?

I leave the HTML stuff to others: SQL is my stuff but not that decorating stuff :)
Was This Post Helpful? 1
  • +
  • -

#7 Mike007  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 7
  • View blog
  • Posts: 332
  • Joined: 30-August 07

Re: Escaping SQL Strings

Posted 05 June 2009 - 06:25 PM

lol! Ok so I forgot the comma :) you don't have to act all C compiler on me (took me a moment to find my error exactly).
Was This Post Helpful? 0
  • +
  • -

#8 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2734
  • View blog
  • Posts: 11,515
  • Joined: 20-September 08

Re: Escaping SQL Strings

Posted 06 June 2009 - 09:13 AM

You can decode html entities using this sort of thing. You should be able to work out encoding from it too:

import java.util.LinkedHashMap;
import java.util.Map;


public class HtmlEntities {
    private static Map<String, Character> m = new LinkedHashMap<String, Character>();

    static {
        m.put("&nbsp;", (char) 160);
        m.put("&iexcl;", (char) 161);
        m.put("&cent;", (char) 162);
        m.put("&pound;", (char) 163);
        m.put("&curren;", (char) 164);
        m.put("&yen;", (char) 165);
        m.put("&brvbar;", (char) 166);
        m.put("&sect;", (char) 167);
        m.put("&uml;", (char) 168);
        m.put("&copy;", (char) 169);
        m.put("&ordf;", (char) 170);
        m.put("&laquo;", (char) 171);
        m.put("&not;", (char) 172);
        m.put("&shy;", (char) 173);
        m.put("&reg;", (char) 174);
        m.put("&macr;", (char) 175);
        m.put("&deg;", (char) 176);
        m.put("&plusmn;", (char) 177);
        m.put("&sup2;", (char) 178);
        m.put("&sup3;", (char) 179);
        m.put("&acute;", (char) 180);
        m.put("&micro;", (char) 181);
        m.put("&para;", (char) 182);
        m.put("&middot;", (char) 183);
        m.put("&cedil;", (char) 184);
        m.put("&sup1;", (char) 185);
        m.put("&ordm;", (char) 186);
        m.put("&raquo;", (char) 187);
        m.put("&frac14;", (char) 188);
        m.put("&frac12;", (char) 189);
        m.put("&frac34;", (char) 190);
        m.put("&iquest;", (char) 191);
        m.put("&times;", (char) 215);
        m.put("&divide;", (char) 247);
        m.put("&Agrave;", (char) 192);
        m.put("&Aacute;", (char) 193);
        m.put("&Acirc;", (char) 194);
        m.put("&Atilde;", (char) 195);
        m.put("&Auml;", (char) 196);
        m.put("&Aring;", (char) 197);
        m.put("&AElig;", (char) 198);
        m.put("&Ccedil;", (char) 199);
        m.put("&Egrave;", (char) 200);
        m.put("&Eacute;", (char) 201);
        m.put("&Ecirc;", (char) 202);
        m.put("&Euml;", (char) 203);
        m.put("&Igrave;", (char) 204);
        m.put("&Iacute;", (char) 205);
        m.put("&Icirc;", (char) 206);
        m.put("&Iuml;", (char) 207);
        m.put("&ETH;", (char) 208);
        m.put("&Ntilde;", (char) 209);
        m.put("&Ograve;", (char) 210);
        m.put("&Oacute;", (char) 211);
        m.put("&Ocirc;", (char) 212);
        m.put("&Otilde;", (char) 213);
        m.put("&Ouml;", (char) 214);
        m.put("&Oslash;", (char) 216);
        m.put("&Ugrave;", (char) 217);
        m.put("&Uacute;", (char) 218);
        m.put("&Ucirc;", (char) 219);
        m.put("&Uuml;", (char) 220);
        m.put("&Yacute;", (char) 221);
        m.put("&THORN;", (char) 222);
        m.put("&szlig;", (char) 223);
        m.put("&agrave;", (char) 224);
        m.put("&aacute;", (char) 225);
        m.put("&acirc;", (char) 226);
        m.put("&atilde;", (char) 227);
        m.put("&auml;", (char) 228);
        m.put("&aring;", (char) 229);
        m.put("&aelig;", (char) 230);
        m.put("&ccedil;", (char) 231);
        m.put("&egrave;", (char) 232);
        m.put("&eacute;", (char) 233);
        m.put("&ecirc;", (char) 234);
        m.put("&euml;", (char) 235);
        m.put("&igrave;", (char) 236);
        m.put("&iacute;", (char) 237);
        m.put("&icirc;", (char) 238);
        m.put("&iuml;", (char) 239);
        m.put("&eth;", (char) 240);
        m.put("&ntilde;", (char) 241);
        m.put("&ograve;", (char) 242);
        m.put("&oacute;", (char) 243);
        m.put("&ocirc;", (char) 244);
        m.put("&otilde;", (char) 245);
        m.put("&ouml;", (char) 246);
        m.put("&oslash;", (char) 248);
        m.put("&ugrave;", (char) 249);
        m.put("&uacute;", (char) 250);
        m.put("&ucirc;", (char) 251);
        m.put("&uuml;", (char) 252);
        m.put("&yacute;", (char) 253);
        m.put("&thorn;", (char) 254);
        m.put("&yuml;", (char) 255);
    }

    // For testing
    public static void main(String[] args) {
	StringBuilder sb = new StringBuilder(args[0]);
	java.util.regex.Matcher m = java.util.regex.Pattern.compile("&[A-Za-z]+;").matcher(sb);

	int matchPointer = 0;
	while (m.find(matchPointer)) {
	    sb.replace(m.start(), m.end(), "" + HtmlEntities.fromHtmlEntity(m.group()));
	    matchPointer = m.start();
	}
	System.out.println(sb);
    }

    public static char fromHtmlEntity(String s) {
	return m.get(s);
    }
}


Was This Post Helpful? 1
  • +
  • -

#9 Mike007  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 7
  • View blog
  • Posts: 332
  • Joined: 30-August 07

Re: Escaping SQL Strings

Posted 06 June 2009 - 11:32 AM

Ok thank you, this works for decoding, but what structure can you use for encoding? In this instance I have to find the right key for a given value.
Was This Post Helpful? 0
  • +
  • -

#10 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2734
  • View blog
  • Posts: 11,515
  • Joined: 20-September 08

Re: Escaping SQL Strings

Posted 06 June 2009 - 02:51 PM

Just reverse the Map
Was This Post Helpful? 0
  • +
  • -

#11 Mike007  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 7
  • View blog
  • Posts: 332
  • Joined: 30-August 07

Re: Escaping SQL Strings

Posted 06 June 2009 - 02:56 PM

yeah I had to write some loops and do a few things to match a value to a key. Just thought there is an easier way. Here is the code if anyone is interested:

public class HtmlEntities 
{
    private static Map<String, Character> map = new LinkedHashMap<String, Character>();

    static 
    {
    	map.put("&quot;", (char) 34);
    	map.put("&amp;", (char) 38);
    	map.put("&lt;", (char) 60);
    	map.put("&gt;", (char) 62);
        map.put(" ", (char) 160);
        map.put("&iexcl;", (char) 161);
        map.put("&cent;", (char) 162);
        map.put("&pound;", (char) 163);
        map.put("&curren;", (char) 164);
        map.put("&yen;", (char) 165);
        map.put("&brvbar;", (char) 166);
        map.put("&sect;", (char) 167);
        map.put("&uml;", (char) 168);
        map.put("&copy;", (char) 169);
        map.put("&ordf;", (char) 170);
        map.put("&laquo;", (char) 171);
        map.put("&not;", (char) 172);
        map.put("&shy;", (char) 173);
        map.put("&reg;", (char) 174);
        map.put("&macr;", (char) 175);
        map.put("&deg;", (char) 176);
        map.put("&plusmn;", (char) 177);
        map.put("&sup2;", (char) 178);
        map.put("&sup3;", (char) 179);
        map.put("&acute;", (char) 180);
        map.put("&micro;", (char) 181);
        map.put("&para;", (char) 182);
        map.put("&middot;", (char) 183);
        map.put("&cedil;", (char) 184);
        map.put("&sup1;", (char) 185);
        map.put("&ordm;", (char) 186);
        map.put("&raquo;", (char) 187);
        map.put("&frac14;", (char) 188);
        map.put("&frac12;", (char) 189);
        map.put("&frac34;", (char) 190);
        map.put("&iquest;", (char) 191);
        map.put("&times;", (char) 215);
        map.put("&divide;", (char) 247);
        map.put("&Agrave;", (char) 192);
        map.put("&Aacute;", (char) 193);
        map.put("&Acirc;", (char) 194);
        map.put("&Atilde;", (char) 195);
        map.put("&Auml;", (char) 196);
        map.put("&Aring;", (char) 197);
        map.put("&AElig;", (char) 198);
        map.put("&Ccedil;", (char) 199);
        map.put("&Egrave;", (char) 200);
        map.put("&Eacute;", (char) 201);
        map.put("&Ecirc;", (char) 202);
        map.put("&Euml;", (char) 203);
        map.put("&Igrave;", (char) 204);
        map.put("&Iacute;", (char) 205);
        map.put("&Icirc;", (char) 206);
        map.put("&Iuml;", (char) 207);
        map.put("&ETH;", (char) 208);
        map.put("&Ntilde;", (char) 209);
        map.put("&Ograve;", (char) 210);
        map.put("&Oacute;", (char) 211);
        map.put("&Ocirc;", (char) 212);
        map.put("&Otilde;", (char) 213);
        map.put("&Ouml;", (char) 214);
        map.put("&Oslash;", (char) 216);
        map.put("&Ugrave;", (char) 217);
        map.put("&Uacute;", (char) 218);
        map.put("&Ucirc;", (char) 219);
        map.put("&Uuml;", (char) 220);
        map.put("&Yacute;", (char) 221);
        map.put("&THORN;", (char) 222);
        map.put("&szlig;", (char) 223);
        map.put("&agrave;", (char) 224);
        map.put("&aacute;", (char) 225);
        map.put("&acirc;", (char) 226);
        map.put("&atilde;", (char) 227);
        map.put("&auml;", (char) 228);
        map.put("&aring;", (char) 229);
        map.put("&aelig;", (char) 230);
        map.put("&ccedil;", (char) 231);
        map.put("&egrave;", (char) 232);
        map.put("&eacute;", (char) 233);
        map.put("&ecirc;", (char) 234);
        map.put("&euml;", (char) 235);
        map.put("&igrave;", (char) 236);
        map.put("&iacute;", (char) 237);
        map.put("&icirc;", (char) 238);
        map.put("&iuml;", (char) 239);
        map.put("&eth;", (char) 240);
        map.put("&ntilde;", (char) 241);
        map.put("&ograve;", (char) 242);
        map.put("&oacute;", (char) 243);
        map.put("&ocirc;", (char) 244);
        map.put("&otilde;", (char) 245);
        map.put("&ouml;", (char) 246);
        map.put("&oslash;", (char) 248);
        map.put("&ugrave;", (char) 249);
        map.put("&uacute;", (char) 250);
        map.put("&ucirc;", (char) 251);
        map.put("&uuml;", (char) 252);
        map.put("&yacute;", (char) 253);
        map.put("&thorn;", (char) 254);
        map.put("&yuml;", (char) 255);
    }

    private static char fromHtmlEntity(String str) 
    {
        return map.get(str);
    }
    
    /**
     * Finds the value and returns the key that corresponds to that value. If value not found
     * returns null.
     * @param value The value to be found.
     * @return The key corresponding to the value that was found or null if value not found.
     */
    private static String findValue(char value)
    {
    	Set<String> keySet = map.keySet();
    	Iterator<String> i = keySet.iterator();
    	String key = i.next(); // key
    	boolean found = false;
    	String result = null;
    	
    	while(i.hasNext() && !found)
    	{
    		if(map.get(key).charValue() == value)
    		{
    			found = true;
    			result = key;
    		}
    		
    		key = i.next();
    	}
    	
    	return result;
    }
    
    /**
     * Converts special characters in ASCII into html entities (e.g. & -> &amp;)
     * @param encode The string to be encoded.
     * @return The encoded string with HTML entities.
     */
    public static String encode(String encode)
    {
    	StringBuilder str = new StringBuilder(encode);
    	String key;
    	int i = 0;
    	
    	// loop over all the characters in the string
    	while(i < str.length())
    	{
    		// try matching a character to an entity
    		key = findValue(str.charAt(i));
    		if(key != null)
    		{
    			str.replace(i, i + 1, key);
    			i += key.length();
    		}
    		else
    		{
    			i++;
    		}
    	}
    	
    	return str.toString();
    }
    
    /**
     * Converts html entities (e.g. &amp;) into real characthers (ASCII characters, e.g. &amp; -> &)
     * @param decode A string to be decoded.
     * @return The string decoded with no HTML entities.
     */
    public static String decode(String decode)
    {
        StringBuilder str = new StringBuilder(decode);
        java.util.regex.Matcher m = Pattern.compile("&[A-Za-z]+;").matcher(str);

        int matchPointer = 0;
        while (m.find(matchPointer))
        {
            str.replace(m.start(), m.end(), "" + HtmlEntities.fromHtmlEntity(m.group()));
            matchPointer = m.start();
        }
        
        return str.toString();
    }
}



And unit test
import static org.junit.Assert.*;
import org.junit.Test;

public class HtmlEntitiesTest 
{
	private final static String decoded = "<img src=\"dir/filename.php?file=1&date=12414\" />";
	private final static String encoded = "&lt;img src=&quot;dir/filename.php?file=1&amp;date=12414&quot; /&gt;";
	
	@Test
	public void testEncondeHtmlEntities()
	{
		assertEquals(encoded, HtmlEntities.encode(decoded));
	}

	@Test
	public void testDecodeHtmlEntities() 
	{
		assertEquals(decoded, HtmlEntities.decode(encoded));
	}

}


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1